Countif or Sumproduct problem

[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?

 

[Mike H]

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

 

[Bob Umlas]

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

 

[Sean Timmons]

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.

 

[becknarr]

AWESOME!! Thanks guys!!

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

 

[Dave Peterson]

I like:

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

I find it less ambiguous.

 

[Peo Sjoblom]

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

 

[Ashish Mathur]

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