COUNTIF is double counting rows with multiple assigned contacts

pixel_ ✭✭
edited 12/09/19 in Formulas and Functions

Hello all,


I am trying to count rows for one department. This department also has sub-departments. In some rows, it has multiple sub-departments assigned to it.

This is my formula:

=COUNTIFS({Requests Range 2 - Status}, Category2, {Requests Range 4 - Department}, (FIND("Merchandising", @cell) > 0))

For example, one row has "Merchandising-Sales" and "Merchandising-Accounting" assigned to it. I want to count this row once, but the current formula is counting is twice. 

Category2 is a status of In Progress. I am trying to count how many rows are In Progress for Merchandising.


Any help would be greatly appreciated. Thank you!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!