Count Distinct Collect Returning 1

earl_bennett

I am trying to populate a metrics sheet from data contained is a source sheet. In this scenario I am trying to get distinct values that fall between two dates.

This is the formula that I am trying to get to work, but it is currently returning 1.

=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Date Logged} >= $[Start Date]@row, {Date Logged} <= $[End Date]@row)))

As a test I tried to simplify using something other than dates.

=COUNT(DISTINCT(COLLECT({PAS Assigned}, {Rating} >= 1)))

In each case the formula is returning 1, when this is not the case. If I create a count(distinct(collect())) formula in the source sheet it works correctly.

Am I doing something wrong?

Thank you,

Genevieve P.

Hi @earl_bennett

Paul is meaning commas between the {range} and the criteria >=$[Start Date]@row

So in your example above:

=COUNT(DISTINCT(COLLECT({Week Number}, {Rating} = 1)))

There's no comma after {Rating}

Try this:

=COUNT(DISTINCT(COLLECT({Week Number}, {Rating}, = 1)))

Cheers,

Genevieve