Countif or Sumproduct problem
 

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?

Countif or Sumproduct problem
 

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

"becknarr" wrote:

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?

Countif or Sumproduct problem
 

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

"Mike H" wrote in message
...
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

"becknarr" wrote:

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?

Countif or Sumproduct problem
 

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" wrote:

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?

Countif or Sumproduct problem
 

AWESOME!! Thanks guys!!



"Bob Umlas" wrote:

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

"Mike H" wrote in message
...
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

"becknarr" wrote:

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?

Countif or Sumproduct problem
 

I like:

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

I find it less ambiguous.



Bob Umlas wrote:

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

"Mike H" wrote in message
...
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

"becknarr" wrote:

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?

--

Dave Peterson

Countif or Sumproduct problem
 

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

"Dave Peterson" wrote in message
...
I like:

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

I find it less ambiguous.



Bob Umlas wrote:

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

"Mike H" wrote in message
...
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

"becknarr" wrote:

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?

--

Dave Peterson

Countif or Sumproduct problem
 

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

"becknarr" wrote in message
...
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?