Countifs and Distinct

Kavs
Kavs ✭✭✭✭
edited 06/07/23 in Formulas and Functions

Hello!

Question: Does anyone know if it is possible to collect unique student and start date information based on preceptor? Attached is an example. The count should come out to 5 but I can't figure out how write the formula. Some our students apply multiple times with the same start date which I would like filtered out but at the same time I don't want to lose the count of the same students that apply with different start dates. Appreciate the help!


Best Answers

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    Hello @Kavs - I am not sure where you want to place your formula, but here's an idea: Create a helper column that combines the student and the start date like this:

    The Helper Join column formula is =Student@row + [Start Date]@row

    The Count formula is =COUNT(DISTINCT([Helper Join]:[Helper Join]))


    Hope that helps!

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    Sure! This is doable in one column, but I'll use two to explain it. Disclaimer, I'm sure there are others who would get there a different way...

    The SumIF formula is =SUMIF([Helper Join]:[Helper Join], [Helper Join]@row, Hours:Hours)

    The SumIfUnique formula is =SumIF@row / COUNTIF([Helper Join]:[Helper Join], [Helper Join]@row) / COUNTIF([Helper Join]:[Helper Join], [Helper Join]@row)

    Hope that helps, Scott

Answers

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    Hello @Kavs - I am not sure where you want to place your formula, but here's an idea: Create a helper column that combines the student and the start date like this:

    The Helper Join column formula is =Student@row + [Start Date]@row

    The Count formula is =COUNT(DISTINCT([Helper Join]:[Helper Join]))


    Hope that helps!

  • Kavs
    Kavs ✭✭✭✭
    edited 09/23/22

    Thank you @Scott Peters!!! That worked! I have another similar question, is it possible to get a SUM of hours with the same criteria? I did the similar format where I created another helper column adding the student name, start date, and hours to use at the distinct identifier and then created another column to flag duplicates but then the sum doesn't capture the 1st entry. Is there a formula that only flags the 2nd, 3rd, 4th, etc duplicates? Appreciate the help!


  • Scott Peters
    Scott Peters ✭✭✭✭✭✭
    Answer ✓

    Sure! This is doable in one column, but I'll use two to explain it. Disclaimer, I'm sure there are others who would get there a different way...

    The SumIF formula is =SUMIF([Helper Join]:[Helper Join], [Helper Join]@row, Hours:Hours)

    The SumIfUnique formula is =SumIF@row / COUNTIF([Helper Join]:[Helper Join], [Helper Join]@row) / COUNTIF([Helper Join]:[Helper Join], [Helper Join]@row)

    Hope that helps, Scott

  • Kavs
    Kavs ✭✭✭✭

    Thanks @Scott Peters !! Appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!