Count based on a condition
I have been struggling on this fomrula for DAYS! In the past, we had three separate spreadsheets but all of them have the same headings so I decided to merge them in one spreadsheet but it means I need to come up with a formula that use distinct and count based on a condition, but I kept getting error -
The formula that I used was -
=COUNTIF({Courses Product}, "Human Capital Management", DISTINCT({Courses Learners}))
But this will not give me the results that i need - the error that I got was incorrect agreement.
I tried to do countifs, and sumif. but was not able to get the results that I need.
What I need is - I need to check if Product column is labeled HCM, and then count the distinct learners - so if they are 3 or 4 courses with the same learners, I only want to count ONCE.
Thanks
Best Answer
-
I think I got it - using count, distinct, and collect - I am verifying if the number is correct -
=COUNT(DISTINCT(COLLECT({Courses Learners}, {Courses Product}, "Human Capital Management")))
So the formula is followed -
For collect: =COLLECT[range, criterion_range1, criterion,[criterion_range2, criterion2,...]
So in another word, I need to find a distinct count of the learners, so the range should be learners, the criteria is in the Courses range so I used Courses for the criterion range and matched it to HCM for the criterion. That's how I get the number. It works.
My original thought was trying to find a distinct count based on criteria, and there were a lot of formulas but none of them worked or are very complicated. I was able to come upon one solution that shows the formula COLLECT, and I looked into it, and it seems to be very straightforward, and it is!
I hope this helps you all who are looking how to count distinct based on criteria!
Answers
-
I think I got it - using count, distinct, and collect - I am verifying if the number is correct -
=COUNT(DISTINCT(COLLECT({Courses Learners}, {Courses Product}, "Human Capital Management")))
So the formula is followed -
For collect: =COLLECT[range, criterion_range1, criterion,[criterion_range2, criterion2,...]
So in another word, I need to find a distinct count of the learners, so the range should be learners, the criteria is in the Courses range so I used Courses for the criterion range and matched it to HCM for the criterion. That's how I get the number. It works.
My original thought was trying to find a distinct count based on criteria, and there were a lot of formulas but none of them worked or are very complicated. I was able to come upon one solution that shows the formula COLLECT, and I looked into it, and it seems to be very straightforward, and it is!
I hope this helps you all who are looking how to count distinct based on criteria!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 445 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!