Google Sheets/Excel for Mac: Occurrence of a root word in the whole column

[gzixwr123]

Hi, how do I calculate the occurrence / number of times of a root word throughout the whole column and apply the formula to the whole column?

Attached is a shortened example. The complete version has more than 10,000 rows.

I use Microsoft Excel for Mac, Version 16.16.27. I would also like to know the solution if I use Google Sheets. Thanks.

[Pepe Le Mokko]

In B2 enter

Please Login or Register  to view this content.

and pull down (Excel version)

[Haluk]

Alternative for MS Excel and Google Sheets;

Please Login or Register  to view this content.

[janmorris]

place this formula in cell B2, and drag down:

Please Login or Register  to view this content.

note that as this checks the whole column (as per your request) ensure the title of the column will never have a match with any data

[Pepe Le Mokko]

IMO it's bad practice using entire column/row references. If the OP turns his range into an XL table it adapts automatically to the range's length

[janmorris]

@Pepe Le Mokko

do you have example that will suit macOS and Google Sheets?

[Pepe Le Mokko]

Perhaps see Post #3 ?

[janmorris]

post #3 does not use the table method that you said will auto expand, and the table method does not auto populate the formulas on new rows in google sheets.

[Flyboy65]

Expanding a bit on what others have suggested, try this formula in cell B2:

=ARRAYFORMULA(IF(LEN(A2:A)=0,,COUNTIF(A2:A,"*" & A2:A & "*")))

This is for Google Sheets

https://docs.google.com/spreadsheets...it?usp=sharing

[Pepe Le Mokko]

post #3 does not use the table method that you said will auto expand,

The solution provided in post #3 is correct and your initial post did not ask for adapting to the number of rows
First create the excel table containing the A and B columns, then enter the given formula in the first cell of col B. (adapt ranges as needed)It will expand automatically to accommodate the range in col A
Now if you delete or add rows the formula will adapt
This being said if you don't need adapting, just enter the formula in the first cell of col B ( adapt ranges) and double click the handle at the lower right corner of the cell selection
All this works for Excel. I don't know about Google Sheets ( except for the formula)