Has anyone every called you genius? If not, let me be the first.
Thanks a ton!


Biff wrote:
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.