Count based on a condition

topazfae
topazfae ✭✭✭
edited 05/03/23 in Formulas and Functions

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

  • topazfae
    topazfae ✭✭✭
    edited 05/03/23 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

  • topazfae
    topazfae ✭✭✭
    edited 05/03/23 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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!