SOLVED

# COUNTIF formula to exclusively count when all criterias are listed.

Copper Contributor

# COUNTIF formula to exclusively count when all criterias are listed.

Hi there. I've been trying to create a formula where it counts the amount of times a row contains ALL the criteria i have set out, and not just count when at least 1 of the criteria is counted.

I created an excel spreadsheet for you to try and create a COUNTIF formula that answers the formula prompt but my organisation doesn't allow me to share to anyone. Hopefully this screenshot gives you an idea of what i'm after. Please get back to me if

Thank you kindly!

5 Replies
best response confirmed by MontyKnoll (Copper Contributor)
Solution

# Re: COUNTIF formula to exclusively count when all criterias are listed.

@MontyKnoll Try this:

=SUM(COUNTIFS(C2:C26,"Group",B2:B26,{"Cold","Room Temp"}))

or use a pivot table.

# Re: COUNTIF formula to exclusively count when all criterias are listed.

It works! I knew there had to be a way. Thank you

# Re: COUNTIF formula to exclusively count when all criterias are listed.

Nevermind. Your formula worked perfectly in my pretend yoga spreadsheet but for my actual spreadsheet not so much. Formula comes up with 0 everytime and there are red brackets in my formula. Could you let me know what i'm doing wrong?

Thank you so much.

# Re: COUNTIF formula to exclusively count when all criterias are listed.

Can't tell without seeing the workbook. Sorry. Are you sure that everything is spelled correctly? Leading or trailing spaces may mess up things pretty badly.

# Re: COUNTIF formula to exclusively count when all criterias are listed.

No problem! I found out that for some reason the formula didn't work when the data was in a different sheet than the formula (even though I did the Sheet2! before the range criteria). So I've kept the data and summary in the same sheet and it seems to be working!

By the way yes one formula had a problem with a hidden space at the end so instead of typing out the criteria i'm just going to copy paste each time.

Let me know if you might know why your formula may not work between 2 different sheets.

Thank you so much for your help.

1 best response

Accepted Solutions
best response confirmed by MontyKnoll (Copper Contributor)
Solution

# Re: COUNTIF formula to exclusively count when all criterias are listed.

@MontyKnoll Try this:

=SUM(COUNTIFS(C2:C26,"Group",B2:B26,{"Cold","Room Temp"}))

or use a pivot table.