Hello All,

Im trying to set build a formula that will count all dates within one
column that match the criteria for another. For example, with the formula
below, all entries on the 2nd of a month in column B with numbers less than
0, or a range of numbers in column D.

=SUMPRODUCT((B2:B65000="02/05/2008")*(D2:D65000<0))

I keep getting a value of 0. The formula below will count all the dates but
I cant get it to work when I add the range for D column.

=COUNTIF(B2:B65000,"02/05")

Also what would be the wildcard entry for the date if I wanted to show all
entries on the 2nd of any month and year.

Hope you can help.

Thanks

Andy

You are trying to compare numbers with a string. Try it this way for
your first formula:

=SUMPRODUCT((B2:B65000=DATEVALUE("02/05/2008"))*(D2:D65000<0))

or:

=SUMPRODUCT((B2:B65000=--"02/05/2008")*(D2:D65000<0))

or:

=SUMPRODUCT((B2:B65000=DATE(2008,5,2))*(D2:D65000< 0))

To answer your second question, try this:

=SUMPRODUCT((DAY(B2:B65000)=2)*(D2:D65000<0))

Hope this helps.

Pete

On Jun 30, 12:48*pm, O2 andy wrote:
Hello All,

I’m trying to set build a formula that will count all dates within one
column that match the criteria for another. *For example, with the formula
below, all entries on the 2nd of a month in column B with numbers less than
0, or a range of numbers in column D.

=SUMPRODUCT((B2:B65000="02/05/2008")*(D2:D65000<0))

I keep getting a value of 0. *The formula below will count all the dates but
I can’t get it to work when I add the range for D column.

=COUNTIF(B2:B65000,"02/05")

Also what would be the wildcard entry for the date if I wanted to show all
entries on the 2nd of any month and year.

Hope you can help.

Thanks

Andy

Try this:
=SUMPRODUCT(--(B2:B65000=DATE(2008,5,2)),--(D2:D65000<0),D2:D65000)
Regards,
Stefi

€žO2 andy€� ezt Ã*rta:

Hello All,

Im trying to set build a formula that will count all dates within one
column that match the criteria for another. For example, with the formula
below, all entries on the 2nd of a month in column B with numbers less than
0, or a range of numbers in column D.

=SUMPRODUCT((B2:B65000="02/05/2008")*(D2:D65000<0))

I keep getting a value of 0. The formula below will count all the dates but
I cant get it to work when I add the range for D column.

=COUNTIF(B2:B65000,"02/05")

Also what would be the wildcard entry for the date if I wanted to show all
entries on the 2nd of any month and year.

Hope you can help.

Thanks

Andy

Great... Worked perfectly..

Thanks.

Andy...

"Pete_UK" wrote:

You are trying to compare numbers with a string. Try it this way for
your first formula:

=SUMPRODUCT((B2:B65000=DATEVALUE("02/05/2008"))*(D2:D65000<0))

or:

=SUMPRODUCT((B2:B65000=--"02/05/2008")*(D2:D65000<0))

or:

=SUMPRODUCT((B2:B65000=DATE(2008,5,2))*(D2:D65000< 0))

To answer your second question, try this:

=SUMPRODUCT((DAY(B2:B65000)=2)*(D2:D65000<0))

Hope this helps.

Pete

On Jun 30, 12:48 pm, O2 andy wrote:
Hello All,

Im trying to set build a formula that will count all dates within one
column that match the criteria for another. For example, with the formula
below, all entries on the 2nd of a month in column B with numbers less than
0, or a range of numbers in column D.

=SUMPRODUCT((B2:B65000="02/05/2008")*(D2:D65000<0))

I keep getting a value of 0. The formula below will count all the dates but
I cant get it to work when I add the range for D column.

=COUNTIF(B2:B65000,"02/05")

Also what would be the wildcard entry for the date if I wanted to show all
entries on the 2nd of any month and year.

Hope you can help.

Thanks

Andy

That's good to hear, Andy (I was uncertain about some of your
description). Thanks for feeding back.

Pete

On Jun 30, 1:31*pm, O2 andy wrote:
Great... *Worked perfectly..

Thanks.

Andy...