From: Joe User on 28 Nov 2009 18:33 "T. Valko" wrote:> Can't use AND in this application. Thanks. My bad! For i = 1 to 10000: Debug.Print "I will always test my solutions": Next. I should have written: =AVERAGE(IF((MONTH(A1:A6)=10)*(B1:B6="reported")*ISNUMBER(SEARCH("john",C1:C6)), D1:D6)) Again, that's an array formula. See the notes in my original posting. Also note that this assumes that A1:A6 contains actual dates (serial numbers), formatted as d/mm/yyyy. JRD wrote:> the answer for the example would be 4+2 > divided by 2 = 3 I don't think so. The only lines that meet all 3 conditions are lines 4 and 6. The average is (2+2)/2 = 2. ----- original message ----- "T. Valko" wrote in message news:uEDaxsFcKHA.2188(a)TK2MSFTNGP04.phx.gbl...> >=AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", >>ISNUMBER(SEARCH("john",C1:C6))), D1:D6) > > Can't use AND in this application. AND returns a single result where you > need an array of results. > > -- > Biff > Microsoft Excel MVP > > > "JoeU2004" wrote in message > news:eg1XmoDcKHA.1648(a)TK2MSFTNGP05.phx.gbl... >> "JRD" wrote: >>> How can I average the numbers in column D, >>> but only the ones in rows where A = October, >>> B = reported and C contains "John"? >> >> The following array formula: >> >> =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", >> ISNUMBER(SEARCH("john",C1:C6))), D1:D6) >> >> Note: An array formula is committed by pressing ctrl+shift+Enter instead >> of just Enter. The entire formula will be enclosed in curly brackets, >> i.e. {=formula}. If you make a mistake, press F2 to edit, then press >> ctrl+shift+Enter. >> >> >>> Note that there are some text strings in column D, >>> so it is not all numbers >> >> AVERAGE will automatically ignore cells with text strings. But if D3 is >> actually the Excel error #NA instead of the string "NA", you will need >> another term to ignore it. Namely: >> >> =AVERAGE(IF(AND(MONTH(A1:A6)=10, B1:B6="reported", >> ISNUMBER(SEARCH("john",C1:C6)), ISNUMBER(D1:D6)), D1:D6) >> >> >> ----- original message ----- >> >> "JRD" wrote in message >> news:ECCCA3AB-4D0C-4B1C-B245-029E9C6411D2(a)microsoft.com... >>>> Example: >>>> >> > A B C D >>>> >> > 1 01/10/2009 Cancelled John, Steven 4 >>>> >> > 2 01/09/2009 Reported John, Steven 2 >>>> >> > 3 20/10/2009 Reported John, Darren N/A >>>> >> > 4 12/10/2009 Reported John, Darren, Steven >>>> >> > 2 >>>> >> > 5 14/10/2009 Reported Darren, Steven 4 >>>>>>>>6 15/10/2009 Reported John, Darren 2 >>>> > How can I average the numbers in column D, but only the ones in rows >>>> > where A = October, B = reported and C contains "John"? (contains >>>> > John, doesn't have to be exactly John). Note that there are some text >>>> > strings in column D, so it is not all numbers - the answer for the >>>> > example would be 4+2 divided by 2 = 3 >>> >>> > > Thanks >>> >>> >> > > First  |  Prev  |  Pages: 1 2 Prev: Vlookup, COUNTIF, IF or NestedNext: Showing 0 after decimal with ROUND