"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