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))
"Farrel" wrote:
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
|