From: JRD on 28 Nov 2009 06:37 > 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 From: Jacob Skaria on 28 Nov 2009 06:57 Try (in 2007) =AVERAGEIFS(D1:D10,A1:A10,">=" & DATE(2009,10,1),A1:A10, "<" & DATE(2009,11,1),B1:B10,"Reported",C1:C10,"*John*") array formula which will work for 2003/2007 =AVERAGE(IF(TEXT(A1:A10,"mmyyyy")="102009",IF(B1:B10="Reported", IF(ISNUMBER(SEARCH("John",C1:C10)),IF(ISNUMBER(D1:D10),D1:D10))))) If this post helps click Yes --------------- Jacob Skaria "JRD" wrote: > > 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 > > From: JoeU2004 on 28 Nov 2009 09:50 "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 > > From: T. Valko on 28 Nov 2009 13:47 >=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 >> >> > From: Pete_UK on 28 Nov 2009 14:33 Perhaps you could use: =SUMIFS( ... ) / COUNTIFS( ... ) with the same conditional parameters in the two functions. (Sorry, I don't have XL2007, so am unsure of the syntax). Hope this helps. Pete On Nov 28, 11:37 am, JRD wrote:> > 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  |  Next  |  Last Pages: 1 2 Prev: Vlookup, COUNTIF, IF or NestedNext: Showing 0 after decimal with ROUND