Forum Discussion
Using pivot table to count occurrence of text
Hello. I am trying to find out how many times a word/combination of words occurs in a spreadsheet. I tried the COUNTIF function, but it only calculated how many times the word(s) occurred when they were the only words in a cell - it did not calculate how many times the word(s) occurred when they were not the only words in any given cell (i.e., if I was looking for the word "apple", it counted how many times "apple" appeared alone in a cell, but did not count occurrences of "apple pie", "apple juice", etc.).
I selected cells from one column from my spreadsheet and created a pivot table instead to try to find how many total times the word "apple" appears in the spreadsheet, and that one column is now a Field in my pivot table. I have no idea where to go from here.
Hoping for guidance on 1) where to drag my field name within the pivot table (Value box? Rows box?), and 2) once I have it the pivot table set up correctly, how to use it to search for specific text so that I can determine a total number of times that that text appears.
Many thanks for your assistance!
5 Replies
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.
