CountIfs with criteria for location and month, return number of distinct users

Has anyone figured out how to use Countifs to define 2 ranges and criteria, then have the result be a number of distinct names (3rd column)

I have read many of the posts, but I don't see anything that shows how to do this.

Assume I have 2000 lines of data, I need to know the total number of distinct colleagues for the CTC within the month specified.

CTC - 301-Renton

Month - Jan

#Distinct Colleagues = ?

Find more posts tagged with

Accepted answers

All comments

Shimanta Roy

To find the number of distinct colleagues, try the following formula:

=COUNT(DISTINCT( COLLECT(ID:ID, CTC:CTC, "301-Renton", Month:Month, "Jan")))

I suggest you to create two dropdown fields in the sheet summary for CTC and Month. So that youcan change the values anytime and find the corresponding counts. If you create those fields in sheet summary then use the following formula to get the count:

=COUNT(DISTINCT( COLLECT(ID:ID, CTC:CTC, CTC#, Month:Month, Month#)))

Assuming, the sheet summary fields are named as CTC and Month.

Becky Wilson

Returns a value of 0. Also not sure how ID:ID gets me colleague data so I used Colleague:Colleague instead

Melissa Yamada

Hello @Becky Wilson

=COUNT(DISTINCT( COLLECT(Colleague:Colleague, CTC:CTC, "301-Renton", Month:Month, "Jan")))

should work.

If it returns "0" value, can you share a screenshot of your formula and where you put it?

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.