How to calculate the amount of times a name appears in a list?

Hi, I am trying to see which top 3 names appears in a list of 100 names. Is there a formula I can use to do this?

I know there is a =COUNTIF formula but I would have to put the "NAME" in the formula which I really don't want to do for every name.

I was hoping there is something like a duplicate word counting formula out there?

Find more posts tagged with

Accepted answers

All comments

Mark.poole

@mande you a helper row with the following formula

=RANKEQ([Name Column]@row, [Name Column]:[Name Column], 0)What this does is ranks the names.

Kelly Moore

Hey @Mandeep and @Mark.poole

RankEQ and RankAVG require numeric fields, which means you will need an additional helper column to first calculate the the count.

You can use the COUNTIFS([Name Column]:[Name Column],[Name Column]@row) to find that count. You could then use this helper column in the RankEQ or RankAvg function.

If you only want the top 3 counts, as an alternative, you could build 3 summary fields on your sheet for this. You would change the boldfaced number 1 to 2 or 3 for the respective 2 and 3 positions.

=INDEX(COLLECT([[Name Column]:[Name Column], [Helper CountIfs]:[Helper CountIfs], @cell = LARGE([Helper CountIfs]:[Helper CountIfs], 1)), 1)

Kelly

Mandeep

Does this way mean I have to reference each individual separately?

My sheet looks something like this :

( This is a report that gets done every week, so I would be comparing mulitple reports such as below

Name Option 1 Option 2 Option 3

John Active

George Active

Sam Active

Kelly Moore

@Mandeep

Actual screenshots would help us

Are you saying your sheet could look like this?

Name	Option1	Option2	Option3
John	Active
Geoge		Active
Sam			Active

When you say you will be comparing different reports would do you mean?

Please @mention me to call me out

Kelly

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.