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

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:B 10="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

"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

=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

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

"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")*I SNUMBER(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
...
=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