Count based on a condition

topazfae

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

topazfae

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!