Count unique projects

Rwatkins15

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!

Gia Thinh

Hi @Rwatkins15,

Try this combination formula using COUNT, DISTINCT and COLLECT as below.

=COUNT(DISTINCT(COLLECT([Project name]:[Project name]; Workstream:Workstream; "X")))

Replace "X" with "Y" when counting workstream Y.

Hope that helps.

Sameer Karkhanis

Try this formula in your Qty of Projects column (assuming this is your Sheet 2),

=COUNT(DISTINCT(COLLECT({Project Initiative Name Range Sheet 1}, {Workstream Range Sheet 1}, [Workstream]@row)))

Gia Thinh

Hi @Rwatkins15,

Try this combination formula using COUNT, DISTINCT and COLLECT as below.

=COUNT(DISTINCT(COLLECT([Project name]:[Project name]; Workstream:Workstream; "X")))

Replace "X" with "Y" when counting workstream Y.

Hope that helps.

Sameer Karkhanis

Try this formula in your Qty of Projects column (assuming this is your Sheet 2),

=COUNT(DISTINCT(COLLECT({Project Initiative Name Range Sheet 1}, {Workstream Range Sheet 1}, [Workstream]@row)))