Count unique projects


I want to count the number of unique projects under a particular workstream. For example, a workstream might have three projects under it, but each of those projects might have three people assigned to it. That's 9 occurrences in that workstream, but only three unique projects. How can I count the unique projects under that workstream? I have tried combinations of COUNT, COUNTIF, COUNTIFS, COLLECT, DISTINCT. Haven't figured it out. Below is a screen shot of the data. I want the results to show one instance of Diamond, one instance of Haemus, one instance of Zeus, then count them (three total here) and say there are three projects for HR - M&A. My formula will go on another sheet and the count will be for each of the workstreams (second picture). Thank you!

Best Answers