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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!