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"))

Tags:

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"))

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"))

• edited 01/29/24

Thanks so much for the clue! I've just updated the formula and it's working wonderfully.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!