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

Copper Contributor

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

Thanks!

3 Replies

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

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

This formula returns the intended result in my sheet.

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

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

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

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