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

Find more posts tagged with

Formulas

Accepted answers

ProDevWSL

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

All comments

ProDevWSL

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

Katharyn Taylor

https://community.smartsheet.com/discussion/comment/416182#Comment_416182

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.