Countifs with multiple criteria in single criteria range

**MCP313** · 09-21-2009, 12:37 PM

I am trying to use the countifs function to get a result. In my case there are multiple expressions that I want in a single criteria range. However, when I create the second expression the count goes to zero.

EX: COUNTIFS(M1:M75, "C") yields count of 17

COUNTIFS (M1:M75, "C", M1:M75, "A") yields count 0

Is there a rule that you can not count multiples against the same criteria range?

**DonkeyOte** · 09-21-2009, 12:40 PM

Correct, it is in effect an AND clause and a cell cannot be both C & A simultaneously hence 0.

=SUM(COUNTIF(M1:M75,{"C","A"}))

**MCP313** · 09-21-2009, 02:46 PM

Many thanks, that did the trick!!

**a1b2c3d4e5f6g7** · 11-29-2012, 05:18 PM

=SUM(COUNTIF(M1:M75,{"J21","K22"})

Is there an option to let the red section shift to J22 and K23 if you go to the next cell?

**a1b2c3d4e5f6g7** · 11-29-2012, 05:25 PM

=SUM(COUNTIF(M1:M75,{"J21","K22"})

Is there an option to let the red section shift to J22 and K23 if you go to the next cell?

**icestationzbra** · 12-01-2012, 02:28 AM

since your input parameter cells (red section in your example) are sharing neither row nor column, it is probably best to split the formula this way:

=sum(countif($m$1:$m$75,$j21),countif($m$1:$m$75,$k22))

when you drag this formula down, J21 and K22 will get incremented to J22 and K23.

keep in mind, curly braces { } are for ARRAY CONSTANTS only (like in DonkeyOte's example above, only constant values such as "C" and "A" can reside within them); you cannot put cell addresses or formulae, or the like, within them...

it is difficult to visualise without a sample file, but i hope you can figure the rest of the way out.

991

**skexcelforum** · 10-15-2014, 03:39 AM

I have a similar problem.
My 2 criterias are text values.
How do I do it?

**kaniazhagiri** · 06-10-2015, 02:20 AM

This solved my problem partially =SUM(COUNTIF(M1:M75,{"C","A"})) . to this result I have to further apply this function to get desired result
COUNTIF(M:M,"RESOLVED"). How do I combine both?

**SUHAS KARHADKAR** · 12-01-2016, 03:46 AM

Dear All,

I have seen this thread "=SUM(COUNTIF(M1:M75,{"C","A"}))".
In my worksheet I used this method, but what if some cell in crite range has the double value,
means I have single criteria range with multiple criteria e.g. criteria "P" has value 1, criteria "PP" has value 2,
criteria "P1" has value 0.125 then how to define it?

Please help.

Thanx in advance.

Suhas

**FDibbins** · 12-01-2016, 03:51 AM

SUHAS KARHADKAR welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.