sumproduct to count two arguments
Try this:
Just a hunch that this is what you're trying to do:
=SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1))
You want to count based on *EVERY* EOM date?
Biff
wrote in message
ps.com...
Gentlemen,
Thanks a ton for the responses. Though neither of them worked out the
way I wanted. The result is supposed to be six and I got 1. There
aren't any empty cells in either of the ranges. I'm thinking that I
may have an issue with date formatting..should it be in serial format?
I have been working on this problem for a few days now, asked numerous
people and I'm beginning think it is impossible.
Does anyone want to take a look at the workbook?
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.
|