COUNTIFS formula with several criteria not feeding into metrics correctly

Cayla Davis

We are trying to map out our issues that have been reported each month, with various criteria, and how many have been closed each month. I can see the data in my report, but i cant get it to feed over into my metrics sheet

This is the formula that I am using:

=COUNTIFS({Amadeus: TC Known Issues & Limitations Mas Range 2}, @cell = Airline@row, {Amade: TC Known Issues & Limitations Maste Range 2}, HAS(@cell, "NDC"), {Amadeus: TC Known Issues & Limitations Mas Range 3}, HAS(@cell, "Limitation"), {Amadeus: TC Known Issues & Limitations Mas Range 1}, HAS(@cell, "Closed"), {Amadeus: TC Known Issues & Limitations Mas Range 5}, HAS(@cell, "English"), {Amadeus: TC Known Issues & Limitations Mas Range 1}, MONTH(@cell) = 7, {Amadeus: TC Known Issues & Limitations Mas Range 1}, YEAR(@cell) = 2024)

Here you can see all my closed items by month, but what is in the report below that i am using for validation (not source) is not reflecting in my metrics

Paul Newcome

Can you provide a screenshot of the report filters?

My initial thought is that you use {Range 2} twice (first two ranges). I am assuming this is unintentional?

jmyzk_cloudsmart_jp

Hi @Cayla Davis

You only need to use HAS(@cell, "NDC") if the cell contains multiple values, like in a multi-select dropdown or multi-select contact. If the column only has a single value, you can just write "NDC" without using HAS().

Example:  {Type Range}, "NDC"

Your formula

=COUNTIFS(

  {Airline Range}, Airline@row,
  {Type Range}, HAS(@cell, "NDC"),

  {Category Range}, HAS(@cell, "Limitation"),

  {Status/Closed Range}, HAS(@cell, "Closed"),

  {Language Range}, HAS(@cell, "English"),

  {Closed Date Range}, MONTH(@cell) = 7,

  {Closed Date Range}, YEAR(@cell) = 2024

)

https://app.smartsheet.com/b/publish?EQBCT=30bbfb8d6cf14a3395140da4c1c4c92e

I added # helper columns to populate Air Line columns with the following formula;

=IF([#]@row = 0, "ALL", IFERROR(INDEX(DISTINCT({Airline}), [#]@row), ""))

Then, using the Arline value, I used the COUNTIFS formula like the one in bold.

The two IFs, one for "All" and the other for "ISTEXT([Air Line]@row)" are to cope with special cases: one to ignore {Airline} for "All", the other to show nothing if the Airline column is blank.

October 2024 formula

=IF(

  [Air Line]@row = "ALL",

  COUNTIFS(

    {Closed Date}, ISDATE(@cell),

    {Closed Date}, AND(YEAR(@cell) = 2024, MONTH(@cell) = 10),

    {Type}, "NDC",

    {Category}, "Limitation",

    {Language}, "English"

  ),

  IF(

    ISTEXT([Air Line]@row),
    COUNTIFS(

      {Airline}, [Air Line]@row,

      {Closed Date}, ISDATE(@cell),

      {Closed Date}, AND(YEAR(@cell) = 2024, MONTH(@cell) = 10),

      {Type}, "NDC",

      {Category}, "Limitation",

      {Language}, "English"

    )

  )

)

The metrics sheet counts the following sample issue data.

https://app.smartsheet.com/b/publish?EQBCT=ae00509705a94b93bd80a8d55d07b565

Cayla Davis

@Paul Newcome I have no idea why it was like that because the rest of my metrics dont have it lol

I changed it on that one column, but i am still stuck on how to get this to feed properly

Cayla Davis

@jmyzk_cloudsmart_jp sorry, that went over my head haha what would be the formula then?

Paul Newcome

You won't need any helper columns. Give this a try (make sure ranges are correctly entered). It looks like maybe some ranges might have been mixed up. Without knowing exactly which column each of the ranges is pointing to (recommend best practice of renaming cross sheet references), at the very least, I can confirm how the first two should look. I also suggest an IFERROR for the MONTH and YEAR pieces (see bold).

=COUNTIFS({Source Sheet Airline Column}, HAS(@cell, Airline@row), {Source Sheet Graphical Column}, @cell = "NDC", ………….., {Amadeus: TC Known Issues & Limitations Mas Range 1}, IFERROR(MONTH(@cell), 0) = 7, {Amadeus: TC Known Issues & Limitations Mas Range 1}, IFERROR(YEAR(@cell), 0) = 2024)

If those updates don't work, can you provide more details such as specific error messages you are getting or if it is just the count that is off?

jmyzk_cloudsmart_jp

Hi @Cayla Davis

Formula using descriptive range name

=COUNTIFS(

  {Airline}, [Air Line]@row,

  {Closed Date}, ISDATE(@cell),

  {Closed Date}, MONTH(@cell) = 9,

  {Closed Date}, YEAR(@cell) = 2024,

  {Type}, "NDC",

  {Category}, "Limitation",

  {Language}, "English"

)

Formula using your range names. (Best guess)

=COUNTIFS(

  {Amadeus: TC Known Issues & Limitations Mas Range 2}, [Air Line]@row,

  {Amadeus: TC Known Issues & Limitations Mas Range 1}, ISDATE(@cell),

  {Amadeus: TC Known Issues & Limitations Mas Range 1}, MONTH(@cell) = 9,

  {Amadeus: TC Known Issues & Limitations Mas Range 1}, YEAR(@cell) = 2024,

  {Amade: TC Known Issues & Limitations Maste Range 2}, "NDC",

  {Amadeus: TC Known Issues & Limitations Mas Range 3}, "Limitation",

  {Amadeus: TC Known Issues & Limitations Mas Range 5}, "English"

)

As Paul suggested, a formula like "IFERROR(MONTH(@cell), 0) = 7" will work. Mine put a condition like "{Closed Date}, ISDATE(@cell)" before using DATE functions like MONTH() to avoid the errors.

Cayla Davis

Thanks guys! i really appreciate it, i am now trying to get the total number when criteria is met, using something very similar to what you have

=COUNTIFS({AirlineReference}, HAS(@cell, "AFKL"), {Language}, "English", {Status}, AND(@cell <> "Open")), and it's working but it's not taking into consideration the cells that have multiple criteria. It's counting only the cells with AFKL adn not the ones that have other airlines. @Paul Newcome - i used what you recommended and did the HAS function but its still not picking it up like it did before

Paul Newcome

Try getting rid of the status range/criteria set and use a COUNTIFS with just the airline range and the HAS. Then apply a filter to the source data that is essentially the same as this COUNTIFS. Are we picking everything up, or is there a discrepancy?

Cayla Davis

There's a discrepancy in the numbers, its not picking up the ones where multiple criteria is found in the cell

Paul Newcome

Let's try recreating the range from scratch as well as the rest of the formula. That's really odd that the HAS formula isn't picking up the cells that has multiple selections. I use this almost daily with no issues. You may end up needing to reach out to support here soon.

Cayla Davis

I tried rebuilding but i am still getting 9 however, there is others in that AirlineReference but it's not picking those up. I cant see what else I can change on here? Is it my Open field?

=COUNTIFS({AirlineReference}, HAS(@cell, "AFKL"), {Language}, "English", {Status}, AND(@cell <> "Open"))

Paul Newcome

What number do you get when you apply a filter like this to the source sheet?

Airline is one of AFKL

Language is equal to "English"

Status is not one of Open

Cayla Davis

9 but its not counting the cells that contain other criteria when AFKL is included

Paul Newcome

So the filter is showing 9 rows? But of the row shown by the filter, none of them are rows that have multiple airlines? Do you have a screenshot of the filter? Is your most recent screenshot the rows shown when the filter is applied?