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

Copper Contributor



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).


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.


Data Set (Simplified)



Old Process




3 Replies

@Bethany Gugino 



This formula returns the intended result in my sheet.


@Bethany Gugino 

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




@Bethany Gugino  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)))