# Count Distinct Collect Returning 1

Options
✭✭✭✭✭✭

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,

• Employee
Options

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

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

There's no comma after {Rating}

Try this:

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

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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)))

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

I believe I do... See attached.

• Employee
Options

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

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

There's no comma after {Rating}

Try this:

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

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

Thank you so much!

• Employee
Options

No problem! I'm glad we could help 🙂