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

Kenny Dougherty

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!

Paul Newcome

Try this...

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

Paul Newcome

Try this...

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

Kenny Dougherty

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

Paul Newcome

Happy to help. 👍️