Date X Data

[Guest]

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

 

[Dave R.]

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.

 

[Guest]

the subtotal (1... will give you 5.5 effectively as a constant
you have three times when the rest of the equaiton is met, therefore you
are adding three 5.5 to give you 16.5

to use sumproduct to get the average
=SUMPRODUCT(A1:A10*(MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))/
=SUMPRODUCT((MONTH(B1:B10)=5)*(YEAR(B1:B10)=2005))