Countif or Sumproduct problem

  • Thread starter Thread starter becknarr
  • Start date Start date
B

becknarr

I am trying to count the number of "males" within a certain date.

When I first started, I only had one date to pick from, so my formula was:
=COUNTIF(E8:E56,"M")

But now that I have multiple dates to choose from I tried using this function:
=SUMPRODUCT(--(M8:M56="10/2/2008")--(E8:E56="M"))

Any suggestions?
 
Drop the quotes

=SUMPRODUCT(--(M8:M56=10/2/2008)--(E8:E56="M"))

but i'd use

=SUMPRODUCT(--(M8:M56=a1)--(E8:E56="M"))

With my date in a1

Mike
 
No---
=SUMPRODUCT(--(M8:M56=10/2/2008)--(E8:E56="M"))
would evaluate the date as 10 divided by 2, divided by 2008, not as a date.
You need this:
=SUMPRODUCT(--(M8:M56=DATEVALUE("10/2/2008")),--(E8:E56="M"))
Bob Umlas
Excel MVP
 
Couple of thoughts.

In between your ) and -- enter a ,

May want to reference a cell with the date rather than hard-coding it into
your formula. Formulas don't do dates like that very well.
 
AWESOME!! Thanks guys!!



Bob Umlas said:
No---
=SUMPRODUCT(--(M8:M56=10/2/2008)--(E8:E56="M"))
would evaluate the date as 10 divided by 2, divided by 2008, not as a date.
You need this:
=SUMPRODUCT(--(M8:M56=DATEVALUE("10/2/2008")),--(E8:E56="M"))
Bob Umlas
Excel MVP
Click to expand...
 
I like:

=SUMPRODUCT(--(M8:M56=date(2008,10,2)),--(E8:E56="M"))

I find it less ambiguous.
 
Plus the DATEVALUE doesn't do anything that a VALUE or a -- will do

=SUMPRODUCT(--(M8:M56=--"10/2/2008"),--(E8:E56="M"))


would have worked but only in Excel with that date format, if one would use
the date
string then this is better


=SUMPRODUCT(--(M8:M56=--"2008-10-02"),--(E8:E56="M"))

Otherwise I agree with you, use the DATE function


--


Regards,


Peo Sjoblom
 
Hi,

you can also enter the following array formula (Ctrl+Shift+Enter)

=SUM(IF((A1:A5=DATEVALUE("10-2-2008")*(B1:B5="M")),1))

--
Regards,

Ashsih Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Back
Top