countif using multiple criteria

[Guest]

I am trying to calculate how many times an event happens with multiple
criteria. As i understand it COUNTIF will not work. But SUMPRODUCT is the way
to go. I have tried this but am unable to get to work.

I am trying to find out how many time "new" occurs in a given month say from
1st Jan to 31st Jan so my formula read
=sumproduct((J20:J100>=01/01/06),(j20:j100<=31/01/06),(g20:G100="new")
What am I doing wrong?

 

[Peo Sjoblom]

=SUMPRODUCT(--(J20:J100>=--"1/1/6"),--(J20:J100<=--"31/1/6"),--(G20:G100="new"))

however a better way would be

=SUMPRODUCT(--(J20:J100>=DATE(2006,1,1)),--(J20:J100<=DATE(2006,1,31)),--(G20:G100="new"))

since it is not region centric regarding the date format

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Biff]

Hi!

Try this:

A1 = 1/1/2006
B1 = 1/31/2006
C1 = new

=SUMPRODUCT(--(J20:J100>=A1),--(J20:J100<=B1),--(G20:G100=C1))

Biff

 

[Guest]

Thanks Peo
I tried your suggestion but it is still not giving me the correct result.

 

[Dave Peterson]

I think it's giving the correct solution--but maybe your data isn't what you
expected it to be. (And your range is correct, right?)

Another option that will return the same value as Peo's formula:

=sumproduct(--(text(j20:J100,"yyyymm")="200601"),--(g20:g100="new"))

=====
You could apply data|filter|autofilter to that range. Filter to show just the
january dates and then filter to show the "new" rows.

I think you'll see that all the formulas give the same result as what you see.

======
If all this doesn't work, post the formula you're using.