COUNTIFS Using both AND and OR
Hi,
Looking for a bit of help trying to utilize the COUNTIFs function to filter based on a specific value for one variable and between two other values for the other variable.
In the sample table below, I'm looking to get a count where the TASTE is PREFERRED and the ALLERGIES is either YES or UNKNOWN.
I've tried the below but am getting an INCORRECT ARGUMENT SET
=SUM(COUNTIFS(Taste:Taste, "Preferred", Allergies:Allergies, "Yes", "Unknown"))
Best Answer
-
Hi Katharyn!
You are close, you'll need an OR statement in your Criterion 2 space:
=COUNTIFS(Range 1, Criterion 1, Range 2, Criterion 2,...)
Range 1 = [Taste]:[Taste]
Criterion 1 = "Preferred"
Range 2 = [Allergies]:[Allergies]
Criterion 2 = OR(@cell="Yes", @cell="Unknown")
so your final formula will be:
=COUNTIFS([Taste]:[Taste], "Preferred", [Allergies]:[Allergies], OR(@cell="Yes", @cell="Unknown"))
Answers
-
Hi Katharyn!
You are close, you'll need an OR statement in your Criterion 2 space:
=COUNTIFS(Range 1, Criterion 1, Range 2, Criterion 2,...)
Range 1 = [Taste]:[Taste]
Criterion 1 = "Preferred"
Range 2 = [Allergies]:[Allergies]
Criterion 2 = OR(@cell="Yes", @cell="Unknown")
so your final formula will be:
=COUNTIFS([Taste]:[Taste], "Preferred", [Allergies]:[Allergies], OR(@cell="Yes", @cell="Unknown"))
-
Thanks so much for the clue! I've just updated the formula and it's working wonderfully.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!