Counting text to see how many times it repeats in a column

Question

Hello!&nbsp;I have a spreadsheet with over 1,000 rows that contain names of registrants along with other data. I want to know how many times the person registered (1, 2, or 3 times).&nbsp;In the past, I've copied the names into a new column, removed the duplicates, and then used the countif function to count how many times that name showed in the original data set. I then did a second countif function to show how many times 1, 2, or 3 showed (including screenshots showing this process). If possible, I want to remove this extra step and go right from the original data set to the weeks attended table.&nbsp;Data Set (Simplified)&nbsp;Old Process&nbsp;Thanks!

oliverscheurich · Answer

Bethany Gugino&nbsp;=COUNTIF($B$2:$B$20,E2)/E2&nbsp;This formula returns the intended result in my sheet.

patrick2788 · Answer

Bethany Gugino&nbsp;Another approach would be to create a simple pivot table where 'student' appears in both 'rows' and 'values' giving you a "countif".&nbsp;&nbsp;

mtarler · Answer

Bethany Gugino&nbsp; Maybe this is what you want:=LET(in, Table1[Student Name], 
     uniqNames, UNIQUE(in), 
     counts, SCAN(0,uniqNames, LAMBDA(q,p, SUM(--(in=p)))), 
     maxCount, MAX(counts), 
     freqs, SCAN(0,SEQUENCE(maxCount),LAMBDA(q,p,SUM(--(counts=p)))), 
     VSTACK(HSTACK(SEQUENCE(maxCount),freqs),HSTACK("Total Students", ROWS(uniqNames)))
     )

Forum Discussion

Counting text to see how many times it repeats in a column

Share

Resources