Sumproduct

[Guest]

I can't seem to make SUMPRODUCT work with a date field as a criteria.

SUMPRODUCT(range="x")*(range="9/1/07")
I would like for the date range to look for "*/5/*" ie. amy month, any year.
If I change the range and look for words in that range it works. I just
can't seem to get it to work with a date.

 

[Pete_UK]

Try this:

=SUMPRODUCT((range="x")*(MONTH(range)=5))

Don't forget the parentheses!!

Assumes dates are in Excel date format.

Hope this helps.

Pete

 

[Guest]

.. date range to look for "*/5/*" ie. any month, any year

One way is to use TEXT on the dates col

With real dates assumed in col B,
you could try something like this in say, C2:
=SUMPRODUCT((A2:A100="x")*(TEXT(B2:B100,"d")="5"))

 

[Guest]

or just

=SUMPRODUCT((A2:A100="x")*(DAY(B2:B100)=5))

 

[Pete_UK]

Sorry, I mis-read what you wanted, as in the UK the middle part of the
date is the month. If you want the fifth day then change MONTH to DAY
in my formula.

Pete