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" <JRD(a)discussions.microsoft.com> 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" <JRD(a)discussions.microsoft.com> wrote in message news:ECCCA3AB4D0C4B1CB245029E9C6411D2(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" <joeu2004> wrote in message news:eg1XmoDcKHA.1648(a)TK2MSFTNGP05.phx.gbl... > "JRD" <JRD(a)discussions.microsoft.com> 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" <JRD(a)discussions.microsoft.com> wrote in message > news:ECCCA3AB4D0C4B1CB245029E9C6411D2(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 <J...(a)discussions.microsoft.com> 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 Nested Next: Showing 0 after decimal with ROUND 