=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
...
"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
...
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