COUNTIF formula to exclusively count when all criterias are listed.

[MontyKnoll]

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!

[#2 · Accepted solution]

@MontyKnoll Try this:

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

or use a pivot table.

[#3]

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

[#4]

@Riny_van_Eekelen 

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.

[#5]

@MontyKnoll 

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.

[#6]

@Riny_van_Eekelen 

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.