COUNTIFS with OR

Lisa Lewis · 01/06/21

I am trying to solve for a formula to countifs all conditions, except for one that I want to count if either/or:

=COUNTIFS({HR Projects - Project Stage}, Metrics16, {HR Projects - PMO Must-Do}, 1, {Range 4}, 0, (OR{HR Projects - PMO Range 7}, "Inclusion & Diversity", "Employee Experience")

Thank you!

Kelly Moore · 01/06/21

Hi Lisa

Try this

=COUNTIFS({HR Projects - Project Stage}, Metrics16, {HR Projects - PMO Must-Do}, 1, {Range 4}, 0, {HR Projects - PMO Range 7}, OR(@cell="Inclusion & Diversity", @cell="Employee Experience"))

Also, to make your formulas even more robust, consider using @row in place of specific row numbers whenever possible. ex: Metrics@row Click here for more info

Kelly Moore · 01/06/21

Hi Lisa

Try this

=COUNTIFS({HR Projects - Project Stage}, Metrics16, {HR Projects - PMO Must-Do}, 1, {Range 4}, 0, {HR Projects - PMO Range 7}, OR(@cell="Inclusion & Diversity", @cell="Employee Experience"))

Also, to make your formulas even more robust, consider using @row in place of specific row numbers whenever possible. ex: Metrics@row Click here for more info

Lisa Lewis · 01/06/21

That was SO QUICK and WORKED....THANK YOU!!!!

Kelly Moore · 01/06/21

That was because you were already 99% of the way there. Glad to help with a small tweak.

Kelly

PeggyLang · 12/16/22

@Kelly Moore

Hello Kelly,

Wonder if you can figure out why my COUNTIFS is not working??

formula is

=COUNTIFS([22 Jan]@row:[22 Dec]@row, "❌", [22 Jan]@row:[22 Dec]@row, "☢️")

If I only count instances of ❌ my 'COUNTIF' formula works, but when I add the other criteria it doesn't work - returns a '0'.

I'm stumped.

Kelly Moore · 12/16/22

Hey @PeggyLang

Happy to help. Let's see if an OR will help

=COUNTIFS([22 Jan]@row:[22 Dec]@row, OR(@cell="❌", @cell="☢️"))

Does this work for you?

Kelly

PeggyLang · 12/16/22

@Kelly Moore

YUP! Works perfectly! I tried the 'OR', however had the syntax WAY off for it to work.

Thank you! Have a greate weekend.

COUNTIFS with OR

Best Answer

Answers

Help Article Resources

Categories