CODEWITHSUNDEEP
google-sheets
Sam
Sam

Reputation: 31

Count the Occurences of a text for the whole spreadsheet

I have a spreadsheet where in every column I have a list of names. They can repeat, and whats even worse - cells can contain some additional text (apart from the name).

What I want to do is count the occurrences of the name in the whole spreadsheet (only looking at the name, omitting the potential additional text). Is Possible?

I tried the formula =UNIQUE, but it does not work vertically (I'm working with Google Sheets)

Example of a document https://docs.google.com/spreadsheets/d/1STtJr0yisSeuv2w8_JVgQABAL5EDzI8aFmH8Vp2cOko/edit?usp=sharing

Upvotes: 1

Views: 250

Answers (2)

Tedinoz
Tedinoz

Reputation: 7984

I'm sure that others will provide a much more elegant solution but this takes the data as presented in the spreadsheet and can be implemented in just a few minutes.

  1. Paste this formula in Cell F3

    =FILTER({A3:A13;B3:B6;C3:C5;D3:D5;E3:E5;A18:A21;B18:B20;C18:C20;D18:D20;E18:E19}, LEN({A3:A13;B3:B6;C3:C5;D3:D5;E3:E5;A18:A21;B18:B20;C18:C20;D18:D20;E18:E19}))

This creates a single column list compiled from the various smaller lists.

  1. Highlight the range of names created in Column F (based on the test data = F3:F41), click Copy,

  2. Highlight cell G3, click Paste special (Paste Values only) - this converts the formula to a list for entries.

  3. Paste this formula in Cell H3 - this removes any data in brackets
    =left(G3,iferror(search(" (",G3)-1,len(G3)))

  4. Copy the formula down as many rows as there is data in Column G

  5. Paste this formula in cell I3 - this lists the unique names
    =unique(H3:H41)

  6. Paste this formula in cell J3 and copy down as many rows as there is data in Column I - this counts the number of instances of each unique name in the master list.
    =COUNTIF(H:H, I3)

enter image description here

Upvotes: 0

Rocky
Rocky

Reputation: 990

You can use Countif, Arrayformula, and Regexreplace to accomplish this task

Assuming you have the data range from A2:E12 and the prefilled unique names starting from A14

Formula:

=countif(ARRAYFORMULA(regexreplace($A$2:$E$12,".\(.*","")),A14)

Copy the formula until the last row

Hope it helps!

Upvotes: 2

Related Questions