Countifs and Distinct

Kavs

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!

Example.pdf

Scott Peters

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

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

Scott Peters

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

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!

Example (2).pdf

Scott Peters

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

Thanks @Scott Peters !! Appreciate your help!