How can I use COUNTIFS with DISTINCT?

ahansen

I'm trying to count all the distinct records in one column where specific criteria is met in another column.

I can write the formulas separately, but I'm unsure how to combine them since COUNTIFS requires <range>,<criteria>.

=COUNT(DISTINCT({Identifier}))

=COUNTIF({Round}, 2)

Find more posts tagged with

Accepted answers

All comments

ahansen

I think I figured this out using COLLECT. This seems to work:

=COUNT(DISTINCT(COLLECT({Identifier}, {Round}, Round@row)))

SoS | Dan Palenchar

Hello @ahansen -

If you're able to share a screenshot of your Sheet and/or elaborate more that would help to help you.

A way you can do this is by using one or more helper column(s) that are used to identify if the criteria you are looking for is met. So one column could check if your Record is a unique name, another could check that your second criterion is met, and then a final column could check to see if both of these criteria are met. This final column could be summed to get the total number of unique Records that have additional criteria satisfied.

In the screenshot below I am using the Sheet Summary field Count to count all of the rows where Records is a unique value (based on Unique Record being checked) AND where Second Criteria is checked. When both of these are true the formula in Distinct + 2nd Criteria gets starred and the Count field counts all of rows where this is starred.

For visual cues, all of the counted rows are shaded green and the rows where the Record is unique but the Second Criteria is not checked is highlighted yellow.

The formulas I'm using are:

In Records: =IF(COUNTIF(Records:Records, Records@row) = 1, 1, 0)
In Distinct + 2nd Criteria: =IF(AND([Unique Record]@row = 1, [Second Criteria]@row = 1), 1)
In Count (Sheet Summary): =COUNTIF([Distinct + 2nd Criteria]:[Distinct + 2nd Criteria], 1)

Note that determine if a Record is distinct I am using an IF(COUNTIF()) formula rather than DISTINCT().

_____________________________________________________________________________________________

👨🏼‍💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

If this response helped you please help me & the community by accepting it and reacting as you see fit (💡insightful, ⬆️ Vote Up, and/or ❤️Awesome).

🆘 Smartsheet Consulting Inquiries: schoolofsheets.com/workwithus

▶️ Smartsheet Tutorial Videos: schoolofsheets.com/youtube

Sign in to join the conversation!

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.