Formula help to count a distinct field when a condition is met

Kerri S

Hello Smartsheet Community,

I'm trying to count the Distinct Project names in a different sheet's column when another column, PI Group, matches [PI Group]@row, but am getting an Incorrect Argument. Can you help me finesse this formula? Thank you!! Below is the formula that's not working and the data it's referencing. -kerri

Paul Newcome

Try

=COUNT(DISTINCT(COLLECT({Product Increment Data Project Name}, {Product Increment Data PI Group}, [PI Group]@row)))

Dale Murphy

@Kerri S I would use Collect() inside the Distinct function to ensure you are only looking at the right subset of project names. The Collect function should let you build the list of projects where PI groups match. The Distinct function will then pick distinct project names, which you can count.

dm

Kerri S

Hi Dale - I'm still gettting Incorrect Argument.

=COLLECT(DISTINCT({Product Increment Data Project Name}, {Product Increment Data PI Group}, [PI Group]@row))

Paul Newcome

Try

=COUNT(DISTINCT(COLLECT({Product Increment Data Project Name}, {Product Increment Data PI Group}, [PI Group]@row)))

Kerri S

Works perfectly, many thank yous!

Paul Newcome

Happy to help. 👍️