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 = ?
Answers
-
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.Senior Business Intelligence Analyst
Augmedix Bangladesh
(A Commure Company)
shimanta@augmedix.com -
Returns a value of 0. Also not sure how ID:ID gets me colleague data so I used Colleague:Colleague instead
-
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?
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!