Multiple Criteria in Formula

I am working on automation around Dashboards, and I am working on my metrics sheet. I have created this formula, but i keep getting UNPARSED.

I'm trying to take anything with 'AF' and anything that is CLOSED and is the month of July in the creation date.

What do i have wrong in this formula?

=COUNTIFS({Amadeus: TC Known Issues & Limitations Mas Range 1}, HAS(@cell, "AF"), {Amadeus: TC Known Issues & Limitations Mas Range 2}, OR(HAS(@cell, "Closed"), OR(MONTH(Amadeus: TC Known Issues & Limitations Mas Range 3) = 7; "July")))

Find more posts tagged with

Formulas

Accepted answers

All comments

Cayla Davis

@AKnight - Do you have any thoughts?

Kelly Moore

Hey @Cayla Davis

=COUNTIFS({Amadeus: TC Known Issues & Limitations Mas AF data column}, HAS(@cell, "AF"), {Amadeus: TC Known Issues & Limitations Mas Closed data column}, "Closed", {Amadeus: TC Known Issues & Limitations Mas Date column}, MONTH(@cell)=7)

I named the ranges so that you would know what columns to select. You will need to build these ranges manually- and it is a good practice to always name your ranges with column names rather than using the generically numbered ranges that smartsheet inserts.

Will this work for you?
Kelly

AKnight

@Cayla Davis expanding off what @Kelly Moore has said, are trying to count when the Mas Range 1 is AF and when Mas Range 2 is Closed OR Mas Range 3 is July? That would require some reworking of Kelly's response to include that.

However, if you are looking for the Mas Range 1 being AF, Range 2 being Closed, AND the month in range 3 being 7 then Kelly's answer will be the way to go - she removed HAS because it's not really needed but can help specify ranges when the column is a dropdown. Keeping HAS would look like:

=COUNTIFS({Amadeus: TC Known Issues & Limitations Mas Range 1}, HAS(@cell, "AF"), {Amadeus: TC Known Issues & Limitations Mas Range 2}, HAS(@cell, "Closed"), {Amadeus: TC Known Issues & Limitations Mas Range 3}, MONTH(@cell)=7)

Note: The difference of AND and OR is AND (naturally added for each addition to a COUNTIFS function) adds restrictions where all conditions must be met, while OR means only one of those conditions must be met to be counted.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.