Forum Discussion
Using pivot table to count occurrence of text
COUNTIF accepts wildcards in the criteria argument:
=COUNTIF(A1:A100, "*apple*")
will return the number of cells that contain the word "apple" possibly together with other text. If the word "apple" occurs more than once in the same cell (e.g. "red apples and green apples"), it will still count as 1.
If you would like to count that as 2, the formula becomes more complicated:
=(SUM(LEN(A1:A100))-SUM(LEN(SUBSTITUTE(A1:A100,"apple",""))))/LEN("apple")
If you don't have Microsoft 365 or Office 2021, confirm the formula with Ctrl+Shift+Enter to turn it into an array formula.
HansVogelaar Many, many thanks for this insight. I tried it and got a different count than when I counted manually, but I will continue to work with it to try to figure out why.
Out of curiosity, would a pivot table also be able to help me determine a count of occurrences? And if so, are you able to tell me which field I would place in which boxes in the pivot table builder to get my total?
I sincerely appreciate your help!
I'm not sure why PivotTable is better, but as variant if we have such tables
load the to data model and add calculated column to Word table
and use it in PivotTable.
SergeiBaklan Thanks very much for your help - I will give this a try!
I don't see how a pivot table would help.
