1

I'm trying to find a formula that will let me verify the frequency of names on a list on a second list without having to go one by one inserting the text in between "".

I'm currently using =Countif(range of list,"(name of person)". But it's taking me a long time to insert each unique name, and there are over 800 names on the list.

Will appreciate any help.

Improve this question
3
  • 1
    Suggestion (not an answer): Though there might be a way to do this is Excel (see exceljet.net/formulas/extract-unique-items-from-a-list), this would be a good query for a database, such as Microsoft Access or LibreOffice Base. The SQL language makes such queries easy, allowing one to SELECT DISTINCT names, and the COUNT of each name. If you have many queries like this, think of using a database, instead.
    – DrMoishe Pippik
    Commented Dec 18, 2023 at 16:25
  • and... Power Query in Excel (if handling larger dataset) - as alternative for excel users
    – Derby9421
    Commented Dec 18, 2023 at 16:58
  • Could you do a simple pivot table of the long list to get the count for each name? If you only want to count the names in a shorter list, then use the results of the Pivot Table as the source for an index/match eg lookup list name 1 in pivot table output and return the value for the count.
    – AdamV
    Commented Dec 19, 2023 at 14:01

1 Answer 1

Reset to default
2

If that's something (or similar) you're after,

enter image description here

Then,

  • Column D, find out unique names by, =UNIQUE(A:A)
  • Column G, work out its frequency by, =COUNTIF($A:$A,D2) (for Alice in this case)
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .