COUNTIFS FUNCTION WITH EMBEDDED CONTAINS FUNCTION

Neela_15

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

KPH

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

KPH

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

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.