Stuck on how to continue with CountIf

Hi,

Select the data and go to Insert > Pivot Table. Check the box of "Add this data to the Data Model". Click on OK. Drag Student_ID to the row labels and badge_tagged to the value area. Right click on any number in the Pivot Table. Under Summarize by, select Distinct Count.

To count how many times each badge appears per unique student ID, you can use a COUNTIFS formula that checks both the student ID in column A and the badge type in column B. This lets you drag the formula down a large dataset without manually filtering anything.

If your StudentID is in A2 and the Badge_Tagged value is in B2, place this formula in a new column (for example C2). It counts how many times that same badge appears for that same student: =COUNTIFS($A:$A,A2,$B:$B,B2)

If you want each student-badge combination to display the total only once (for example, only on the first occurrence), add a condition that checks whether the current row is the first matching instance: =IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,COUNTIFS($A:$A,A2,$B:$B,B2),"")

This will return the total count the first time the student-badge pair appears and leave later duplicate rows blank.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin