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

earl_bennett

Just a quick update...

This gets the distinct count

=COUNT(DISTINCT({Week Number}))

As soon as I add COLLECT into the equation I get the result of 1

=COUNT(DISTINCT(COLLECT({Week Number})))

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

Paul Newcome

None of the formulas should be outputting a number. There should be some kind of error as there are syntax issues. You need to insert commas between each range and its criteria.

earl_bennett

I believe I do... See attached.

formula.png

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

earl_bennett

Ah.... Now I understand and your right. Now I'm returning actual values.

Thank you so much!

Genevieve P.

No problem! I'm glad we could help 🙂