Count unique values based criteria

hannahstayo · 2023-07-11T12:07:18+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Formulas

Accepted answers

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!

All comments

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

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!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.