=COUNTIF(A1:A100,"Houston")
This is a discussion on Need to count number of occurrences of a word in a column within the Excel Questions forums, part of the Question Forums category; Column A has 100 entries (A2 through A101) Some of these entries are "Houston" What formula can I use to ...
Column A has 100 entries (A2 through A101)
Some of these entries are "Houston"
What formula can I use to calc. how many times the word "Houston" appears within that Column so that I don't have to manually count each time???
Thanks for any help.
jase.
=COUNTIF(A1:A100,"Houston")
Geezum's -- you'd think I would have gotten that. I was close. Thanks a million.
jase.
I am also need to count the number of occurences of a particular word across various cells and columns in Excel, however in my case cells don't contain a single word but a large amount of text.
Try array formula, confirm with Ctrl+Shift+Enter:
As this is case sensitive, I included both "with" and "With".Code:=(SUM(LEN(A1:F100))-SUM(LEN(SUBSTITUTE(SUBSTITUTE(A1:F100,"with",""),"With",""))))/LEN("with")
Specializing in Power Query (M).
Using Excel 2007 (Dutch) and Excel 2016 (Office 365 ProPlus) (Dutch/English), both on Windows 10.
Array formulas can be recognised by the {} Excel puts around it automatically when you press Ctrl+Shift+Enter upon formula entry.
Assuming too much and qualifying too much are two faces of the same problem.
Thanks Marcel, that's perfect! Thanks
Interesting idea Aladin, but it will fail to pick up words followed by comma's and other punctuation and for this it's easier to minus the count of withouts from the withs... (as it is I'm picking out certain words and I'm happy for Repair to find repairs and repaired...)
Thanks for the help both.
Like this thread? Share it with others