sumproduct to count two arguments
Biff wrote:
You're use of EOM is evaluating to the NEXT month:
K3 = 1/1/2006
EOMONTH(K3,1) = 2/28/2006
Maybe you want something like this:
=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))
Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a
problem with your second array: ($A$3:$A$151<1).
So, you may want to add another array to the formula to test for that:
=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))
Biff
wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.
I have daily dates from the beginning of this year to now and
corresponding data.
Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:
=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))
I am stuck on the eomonth part..can anyone help?
Many thanks.
|