Need Help with a Formula

Kimberly Washeleski

In a spreadsheet, I will have the same project assigned to multiple individuals (I track projects for Instructional Designers). A Business Unit is assigned to a project. So the way I needed to set up the spreadsheet, the same project will be assigned to multiple individuals (on their own lines - so the project may appear in a spreadsheet 3 times). I now need to do a CountIFs formula, but cannot figure out how to have it indicate that if it is already assigned to a project, then ignore it. I know this sounds more complicated than it may be. Maybe the spreadsheet is set up incorrectly.

This is my current formula, but it accounts for all incidents:

=COUNTIFS([Business Unit]:[Business Unit], "CAI Business", Status:Status, "In Progress")

Kimberly Washeleski

I can't figure out how to edit a question, but basically I am trying to come up with a formula that says

CountIf the Business Unit = XXX

But if more than 1 ID is assigned to the same project, only count the project once