# Countifs with multiple criteria in single criteria range

1. ## Countifs with multiple criteria in single criteria range

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?

2. ## Re: Countifs with multiple criteria in single criteria range

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"}))

3. ## Re: Countifs with multiple criteria in single criteria range

Many thanks, that did the trick!!

4. ## Re: Countifs with multiple criteria in single criteria range

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

5. ## Re: Countifs with multiple criteria in single criteria range

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

6. ## Re: Countifs with multiple criteria in single criteria range

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

7. ## Re: Countifs with multiple criteria in single criteria range

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

8. ## Re: Countifs with multiple criteria in single criteria range

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?

9. ## Re: Countifs with multiple criteria in single criteria range

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?

Suhas

10. ## Re: Countifs with multiple criteria in single criteria range

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.

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1