ExcelBanter - countif using multiple criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - countif using multiple criteria (https://www.excelbanter.com/excel-discussion-misc-queries/66024-countif-using-multiple-criteria.html)

countif using multiple criteria

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?

countif using multiple criteria

=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

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

countif using multiple criteria

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

"Peo Sjoblom" wrote:

=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

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

countif using multiple criteria

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.

Wayne E wrote:

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

"Peo Sjoblom" wrote:

=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

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

--

Dave Peterson