COUNTIFS FUNCTION WITH EMBEDDED CONTAINS FUNCTION

I have been looking at the various conversations on using a COUNTIFS with an embedded CONTAINS function, but have had no success getting it to work. In the below formula I want to count "FAA Domestic" and recommendations that aren't closed (0) if they contain an assigned office that contains AIR-62. The below formula yields #INCORRECT ARGUMENT SET. What am I overlooking? I appreciate your help. Thank you.


=COUNTIFS({Safety Recommendation Domestic}, "FAA Domestic", {Safety Recommendation Closed1}, "0", CONTAINS("AIR-62", {Assigned Office}))

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Neela_15

    The syntax for the COUNTIFS function is range then criteria, range, then criteria, range then criteria. So when you get to the CONTAINS part instead of

    CONTAINS("AIR-62", {Assigned Office})

    you need

    {Assigned Office}, CONTAINS("AIR-62", @cell)

    • {Assigned Office} is your range
    • CONTAINS("AIR-62", @cell) is your criteria

    The full formula looks like:

    =COUNTIFS({Safety Recommendation Domestic}, "FAA Domestic", {Safety Recommendation Closed1}, "0", {Assigned Office}, CONTAINS("AIR-62", @cell))


    Hope this helps

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    Hi @Neela_15

    The syntax for the COUNTIFS function is range then criteria, range, then criteria, range then criteria. So when you get to the CONTAINS part instead of

    CONTAINS("AIR-62", {Assigned Office})

    you need

    {Assigned Office}, CONTAINS("AIR-62", @cell)

    • {Assigned Office} is your range
    • CONTAINS("AIR-62", @cell) is your criteria

    The full formula looks like:

    =COUNTIFS({Safety Recommendation Domestic}, "FAA Domestic", {Safety Recommendation Closed1}, "0", {Assigned Office}, CONTAINS("AIR-62", @cell))


    Hope this helps

  • Neela_15
    Neela_15 ✭✭✭

    Thank you KPH. I tried this formula yesterday and inserted it again today from scratch. In both cases, I received an #UNPARSEABLE error. However, after having my attention diverted elsewhere for about 10 minutes, it suddenly started to work. I am not sure why the delay, but of seen this occur on occasion. Thank you for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!