How to COUNTIFS function with multiple criteria in one column?



I am attempting to use CountIFs via reference sheet. In my Task Log, I have an Issue Type column with multi-select dropdown properties that shows which Firms had issues.

For each instance where an issue is identified, we create a new row in the Task Log with the responsible firm in one column and the issue(s) in another column.

Some firms have multiple, different issues. For example, a firm may have 2 issues such as "Files Missing" or "Incorrect Name Used" within the same column

On a different sheet, I am trying to count the number of times each Issue Type occurs with a single Firm using the reference sheet feature. I also tried doing this on the Sheet Summary within the Task Log too.

I tried using this formula (=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, "Files Missing") but the value returned is always 0 even though the value should much more.

I've tried these formulas as well with no luck:

=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, HAS({Task Log Range 2}, "Files Missing")

=COUNTIFS({Task Log Range 1}, "Firm", {Task Log Range 2}, OR(CONTAINS("Files Missing", {Task Log Range 2})))

What am I doing wrong? Any help is appreciated

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!