Using COUNTIF to count multiple text values in the same column/range

Hey everyone, I'm having a bit of trouble and have browsed through many of the discussions regarding COUNTIF for multiple criteria. I've tried using COUNTIFS, regular COUNTIF and a combination of those two with CONTAINS as well as HAS.

Essentially I am trying to count when criminal charges "Fraud" and "Trade Secrets" both appear in the same row (in the same cell) from a multi-select drop down column. In other words I may track a Fraud lawsuit as seen in the "Charges (single)" column, but I want to have a separate category in my sheet summary that tracks Fraud and Trade Secrets in the "Charges (double)" column.

I know how to track both using a COUNTIFS formula but only when they are in different columns (as you can see in my example in the picture). The Trade Secrets & Fraud section in my sheet summary is counting the row that has "Fraud" in the Charges (single) column and "Trade Secrets" in the Charges (double) column. I need a formula that will count "Fraud" and "Trade Secrets" in the Charges (double) column only.

I hope this makes sense and someone can help me!

Thank you!

Best Answer

• ✭✭✭✭✭✭
Answer ✓

Try this...

=COUNTIFS([Charges (double)]:[Charges (double)], AND(CONTAINS("Fraud", @cell), CONTAINS("Trade Secrets", @cell)))

Answers

• ✭✭✭✭✭✭
Answer ✓

Try this...

=COUNTIFS([Charges (double)]:[Charges (double)], AND(CONTAINS("Fraud", @cell), CONTAINS("Trade Secrets", @cell)))

• Thank you so much Paul, that worked perfectly! I greatly appreciate it!

• ✭✭✭✭✭✭

Happy to help. 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!