Count Distinct Collect only returns 1, even when there should be none
I have this formula for a Sheet Summary field:
=COUNT(DISTINCT(COLLECT(Goal:Goal, [Primary SD]:[Primary SD], ="SET", [Fiscal Year]:[Fiscal Year], ="FY20222023")))
Attached is a screenshot of the sheet. It is filtered to show only rows with Primary SD=SET, and you can see that there are none for FY20222023. So the Sheet Summary field should be zero. I have similar SS fields for other Primary SD's and other years and other targets to count (e.g. Goals, Metrics, and Initiatives). But they all return 1.
I'm stumped.
Answers

I believe all rows will be evaluated by your sheet summary formula, even rows not visible due to a filter.

That makes sense. But the filter was only applied here to demonstrate that the result of the function should be zero, not 1. Why is this result, indeed all results regardless of the actual counts, always 1?

@Karen Harker Your formula works well which makes me think that somehow the error has to do with the fiscal year being a formula column.

Well, that's weird...now the formula works! I guess it needed to think about it overnight ;). Perhaps it needed to be refreshed. Anyhow  all is good.
Thank you both for responding.
Karen
Help Article Resources
Categories
Check out the Formula Handbook template!