Countifs and Distinct
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

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!

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

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!

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!

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

Thanks @Scott Peters !! Appreciate your help!
Help Article Resources
Categories
Check out the Formula Handbook template!