Countifs with Distinct?
I have two columns, Column A and Column B. How can I check Column A for a condition, and then count all the distinct corresponding values in Column B for which that condition is met?
Is there an easy way to do this with Countif / Countifs and Distinct?
Thanks!
Comments
-
Can you provide a little more detail? Are you able to provide screenshots with sensitive/confidential information hidden or replaced with "dummy data"?
.
Here is how I see what you are asking for. Obviously this may not be EXACTLY what you are trying to do, but if you could let me know if this is the right or wrong idea, it will help get us started in the right direction.
.
Column A is a Checkbox. Column B is a list of names.
You want to count how many UNIQUE names are in Column B but only in those that have the corresponding box in Column A checked.
.
Is this kind of along the lines of what you are trying to do?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Everyone,
I have the same problem. It seems that the DISTINCT() function does not work within a COUNTIFS formula. I only seems to work with COUNT.
So currently I can't figure out how to get a count of unique values that also meet another criteria. For example, I have three columns, Email, Region, Program:
david@abc.com | AMER | Program 1
david@abc.com | AMER | Program 2
I would like something like this:
COUNTIFS(DISTINCT({Email}),{Region},="AMER")
Any ideas?
Thanks.
-
The DISTINCT function returns a true/false value. It will work in a COUNTIFS so long as you use the proper syntax.
=COUNTIFS([Column Name]:[Column Name], DISTINCT(@cell) = true)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
Thanks for the follow up. Unfortunately that format does not work either.
I am using cross sheet formulas with a helper sheet and it does not work. I also tried it in the source sheet directly as well, and it gives an "#Incorrect Argument Set" error in both cases.
I suspect that it is not possible to use the DISTINCT function within a COUNTIFS unfortunately.
(By the way, the syntax for COUNTIFS is "range, criterion", so the "(@cell)=TRUE" is invalid also. But I tired it anyway).
Thanks,
David
-
David,
I could have sworn we just recently used DISTINCT within a COUNTIFS as a solution on another post here in the community. After doing further digging I found the post and realized we used COUNT(DISTINCT(COLLECT(.............)))
.
I am not sure though why DISTINCT(@cell) = true cannot be used as a criteria. There are other examples of using similar @cell statements as the criteria once the range was specified that actually worked, but I digress...
.
Anywho...
HERE is a link to the post providing a workaround so that DISTINCT can be used as criteria to count items.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
You accidentally pasted something else in the link so here's the correct link: https://community.smartsheet.com/discussion/collect-if-distinct-challenge
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
That was the only other thing I had opened at the time. Not sure how it got messed up. Haha. Thanks for catching that for me.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Everyone,
Based on the samples provided, I was able to get my scenario solved by using a combination of COUNT, DISTINCT, and COLLECT.
It ended up looking like this:
=COUNT(DISTINCT(COLLECT({Email}, {Geo}, ="AMER")))
Thanks.
-
Happy to help!
Sorry about the initial confusion. Apparently going on memory (even for recent events) isn't my most reliable skill set. Haha. Glad you were able to get it working.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
-
The Count, Distinct, Collect combo worked for me as well! THANK YOU!
-
@Paul Newcome Hi Paul!
I'm trying to use this formula to fix two scenarios
- For this, I need it to return "18" for the number of distinct requests (by name of Requestor) within a specified date range. I had used a different formula previously, but in the case that the same person requests in the same interval, I will need to correct. For some reason, it's returning "1" at the moment with this formula
=COUNT(DISTINCT(COLLECT([Name of Requestor]:[Name of Requestor], NOT(ISBLANK(@cell )), [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))
When I update to this formula, it returns "17"
=COUNT(DISTINCT(COLLECT([Name of Requestor]:[Name of Requestor], [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))
2)
In this situation, I am utilizing the below to find the # of Divisions FS24 Q4. I need it to count distinct entries in the Divisions column in addition to the Please Specify column. It would be great to ensure it doesn't capture "Other, Please specify" from the Division Column as well (as as not to add extra value to the total count, since we are taking into account the entries in the Plea Specify column), but I haven't figured out that piece.
=COUNT(DISTINCT(COLLECT(Division:Division, [Submission Date]:[Submission Date] <= DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2023, 3, 31)))) + COUNT(DISTINCT(COLLECT([Please specify]:[Please specify], [Submission Date]:[Submission Date], <=DATE(2024, 6, 31), [Submission Date]:[Submission Date], >DATE(2024, 3, 31))))
It's currently returning "3" when it should be 12
-
Hmm.. After playing around with both I seem to have made progress however for #1 it is still returning "17" when it should be 18. I'm wondering if it's because there are two "Rachels" in the column, even though they have listed a different last name. I did tests on other date ranges outside of this which the formula works for.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!