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

Nate H

hey @Kelly Moore , do you know if having a vlookup in the count range for the unique values throws off the formula? I had the formula working correctly until I put a vlookup in some of the rows and now it's returning an incorrect count.

Kelly Moore

Hey @Nate H

Without knowing the specifics I can't say for sure, but generally, yes, you can nest a COUNT and lookup together. If you try only the lookup portion of the formula, is it returning the right value?

You may consider switching to the preferred smartsheet lookup combos of Index/Match or Index/Collect. These are considered more robust and more efficient on the sheet.

I'm happy to help if you need help. Is it possible to see your current formula and screenshot(s) of your sheet(s)?

Kelly