Counting Dates - Not Text
 

Ok, then that means your dates are actually TEXT strings.

This expression is testing the range for true Excel dates which are really
NUMBERS formatted to look like dates.

ISNUMBER(DATE!J2:J1116)

So, if your dates are TEXT strings then that expression will return an array
of 0s causing the final result to be 0.

I see Toppers got it straightened out.

--
Biff
Miscrosoft Excel MVP


"Danny" wrote:

Hi Biff,

I'm sorry but it dit not work. I come up with a zero (0).

"Biff" wrote:

Try it like this:

=SUMPRODUCT(--(ISNUMBER(DATE!J2:J1116)),--(TEXT(DATE!J2:J1116,"m")="1"))

--
Biff
Miscrosoft Excel MVP


"Danny" wrote:

Hi,

I'm trying to count the number of "date" occurrences on a particular month.
However in the same column, aside from dates, there are TEXT entries and
blank cells.

When I use the formula below it gives me a #VALUE!

Please edit the formula below so I can count the number of date occurences.

=SUMPRODUCT(--(MONTH('Date'!J2:J1116)=1))

Thank you.