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

Copper Contributor

Hello!

 

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)

BethanyGugino_0-1702488931615.png

 

Old Process

BethanyGugino_1-1702489007149.png

 

Thanks!

3 Replies

@Bethany Gugino 

=COUNTIF($B$2:$B$20,E2)/E2

 

This formula returns the intended result in my sheet.

countif.png

@Bethany Gugino 

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

 

Patrick2788_0-1702491493518.png

 

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