COUNTIF is double counting rows with multiple assigned contacts

pixel_

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!

Paul Newcome

Are Merchandising Accounting and Merchandising Sales both in the same cell?

pixel_

Hi Paul,

 

Yes! They are both in the same cell.

Paul Newcome

Are you able to post some screenshots with dummy data in them? It should not be counting the same cell twice based on your provided formula.

 

For trouble shooting... Use two formulas. In the first one, adjust the ranges so that they stop in the row ABOVE the one being counted twice. In the second formula, set the ranges so that they stop ON the row of the cell being counted twice.

 

This will tell you for absolute certain that this cell is the issue.

pixel_

Thank you for your help, Paul. 

 

Just wanted to let you know that we resolved this. If I recall correctly, I think the issue had to do with how the sheets or formulas were setup. Thanks again for always being helpful!

Paul Newcome

Happy to help and glad you were able to get it working for you!