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
Check out the Formula Handbook template!