You can use array entered:
=AVERAGE(IF(MONTH(B1:B6)=5,A1:A6))
(enter with ctrl-shift-enter at once)
or
if you want to use sumproduct, one way is:
=SUMPRODUCT((MONTH(B1:B6)=5)*(A1:A6))/SUMPRODUCT(--(MONTH(B1:B6)=5))
which does not need to be array entered.
"Farrel" wrote in message
...
I have,
A B
1 5/1/2005
2 5/2/2005
3 5/3/2005
4 6/1/2005
5 6/2/2005
6 6/3/2005
7 7/1/2005
8 7/2/2005
9 7/3/2005
10 7/4/2005
I want the Average of the different months, May for example, why this:
=SUMPRODUCT(SUBTOTAL(1,A1:A10)*(MONTH(B1:B10)=5)*( YEAR(B1:B10)=2005))
is giving me 16.5 ?
TXS
|