# COUNTIFS Using both AND and OR

Options

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:

Options

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

Options

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
Options

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!