# COUNTIFS FUNCTION WITH EMBEDDED CONTAINS FUNCTION

Options
✭✭

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

Tags:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭
Options

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!