COUNT(DISTINCT(COLLECT to count unique values based on multiple criteria

Denisse A

Hi,

I'm trying to count the dates we've worked on a job site, but we have multiple ones. Of course, at first, I'm trying to calculate just for one criterion.

So far, I've tried using the Sheet Summary: =COUNT(DISTINCT(COLLECT(Date:Date, [Site ID]:[Site ID], "CUPERTINO")))

As per my database, I have 80 entries for Cupertino, but counting the days I've worked there for 22 days, and with the formula gives 1 as the value:

Then, I tried counting the days instead of sites:

=COUNT(DISTINCT(COLLECT([Site ID]:[Site ID], Date:Date, "01/01/23")))

How can I count the days I've spent on a job site?

Kelly Moore

Hey@Denisse A

Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.

The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?

Try this

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))

Kelly

Denisse A

Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:

=COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))

Kelly Moore

Hey@Denisse A

Your first formula should return the number of distinct days that equal “CUPERTINO” as the Site ID.

The DISTINCT function will only work when the data being evaluated are all the same type. When mixed types are present, the function will return the value of 1. Is it possible that you have a mixture of Dates and text within your Date column?

Try this

=COUNT(DISTINCT(COLLECT(Date:Date, Date:Date, ISDATE(@cell), [Site ID]:[Site ID], "CUPERTINO")))

Kelly

Denisse A

Thanks Kelly! We found a way on getting the Site ID instead of adding site by site:

=COUNT(DISTINCT(COLLECT(Date,Date,[Site ID]@row)))