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!