Count unique values based criteria

hannahstayo

I want to count the unique values in my data, but based on a specific criteria.

I have set up my data as above, and I want to get the unique count of Date and Handyman, but I would like to do it based on the value in the Month column. I know I can manually select the relevant rows for each month, but ideally if I can set up a formula that takes into consideration the Month column that would be ideal.

The formula I have used to get the distinct count of Date and Handyman is =COUNT(DISTINCT({Date and Handyman}))

Itai Perez

Hey,

The way to use DISTINCT with COUNTIF is this:

=COUNT(DISTINCT(COLLECT({Date and Handyman}, Month:Month,1-Month number you would like to count)))

Hope it works for you.

Here is a link to a previuos discussion on this topic:

https://community.smartsheet.com/discussion/56706/collect-if-distinct-challenge

Let me know if that helped!

Itai Perez

Hey,

The way to use DISTINCT with COUNTIF is this:

=COUNT(DISTINCT(COLLECT({Date and Handyman}, Month:Month,1-Month number you would like to count)))

Hope it works for you.

Here is a link to a previuos discussion on this topic:

https://community.smartsheet.com/discussion/56706/collect-if-distinct-challenge

Let me know if that helped!

hannahstayo

Hi @Itai that worked perfectly! Thank you so much, knew there would be way to do it, just couldn't quite figure it out