Counting Unique Data Only if Associated Column is Not Blank

Nancy Heater

Hello again! We really do research these things before we ask! ;)

We are looking to count the unique Store Numbers for "P1 Complete Dates" and "P2 Complete Dates" in the Sheet's Summary Data, so we can make sure each store is only being counted once for each phase.

The formula that we found and tried is only giving a result of 1, which we know is inaccurate.

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date]), <>""))

Test sheet: https://app.smartsheet.com/b/publish?EQBCT=a3686cd8c2ff4bcc8047b4ba54bc30ce

Thanks for taking a look!

Kelly Moore

@Nancy Heater

Try this

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))

You had a parenthesis too early, which closed off the last criteria too soon.

Kelly Moore

@Nancy Heater

Try this

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))

You had a parenthesis too early, which closed off the last criteria too soon.