COUNTIF formula to exclusively count when all criterias are listed.

Occasional Contributor

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 (Occasional Contributor)

@MontyKnoll Try this:

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


or use a pivot table.

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


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.




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.


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.