Multiple Criteria in Formula

Cayla Davis

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

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.