Forum Discussion

Copper Contributor

Dec 13, 2023

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

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

excel

Formulas and Functions

mtarler

Silver Contributor

Dec 13, 2023

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