How to Count the Number of Times a Word Appears in Excel

Techwalla may earn compensation through affiliate links in this story.
Excel has formulas that allow you to figure out how many times a word appears.
Image Credit: jacoblund/iStock/GettyImages

Excel formulas and functions allow you to extend the application by adding your own operations that are customized for your data. You can use them to perform calculations on specific cells within a spreadsheet or in an external spreadsheet. You can also use them to count duplicate values in Excel, such as the number of times a word appears in a group of cells or in an entire spreadsheet.


Excel Formulas and Functions

An Excel formula is an expression that is entered into a cell by first typing "=." The equal sign is followed by an expression that operates on the values in another cell or range of cells. An Excel function is a built-in formula that performs a common function. Microsoft provides a variety of functions to make spreadsheet creation easier for users.

Video of the Day

An example of a formula is "=A1+A2+A3+A4," which adds the values in cells A1 through A4. This is a perfectly valid formula, but you could shorten it by using the SUM function, which adds the values of two cells or a list of cells. Using the SUM function, you accomplish the same addition as "=A1+A2+A3+A4" by entering "=SUM(A1:A4)."


Use COUNTIF in Excel to Count Frequency of Values

The COUNTIF function in Excel returns the number of times a certain condition is met within a range of cells. One of the common conditions is matching the cell's value to a specific value, which is how it can be used to count the frequency that a value occurs. The value may be a number or a string. Note that when COUNTIF compares strings, it ignores upper and lower case. This means "apples" and "APPLES" will be counted as matching.

When using the COUNTIF function to match a value, you type in a range of cells and the value. For example, "=COUNTIF(A1:A4, "Seattle")" will check cells A1 through A4 and increment a counter if the word Seattle is found. When the function is done it returns the value of the counter. The COUNTIF function can be used to build longer and more complex formulas such as "=COUNTIF(A2:A5, "bananas") + COUNTIF(A2:A5, "oranges")," which adds the number of times the word "bananas" is found to the number of times the word "oranges" is found.


Counting Word Frequency in Rows and Columns

You can use the named range feature in Excel to the number of occurrences in a column or row without specifying a range. Select a column by clicking on the letter at the top or the entire row by clicking on the number at the left. Click on the Define Name button on the Formulas tab and enter a name in the New Name dialog. You can then use this name to refer to all the cells in the row or column.

For example, say you use Define Name to add the name "NamesCol" to a column. You can count all the times the name "Mary" occurs in the cells in the column using the defined name by entering "=COUNTIF(NamesCol,"Mary")." Each time you add a new value to a cell in the NamesCol column, the result of the formula will automatically update.


Counting Characters in a Cell

The COUNTIF function has some limitations, including that you can't use it to count individual characters. The function will search for strings within a cell but not for characters that are in a string in a cell. Instead, use the LEN and SUBSTITUTE functions. To search for all the occurrences of the letter "a" in cell A1, the formula is "=LEN(A1) – LEN(SUBSTITUTE(A1,"a",""))." If cell A1 contains the string "banana," the formula will return 3.


This formula works by taking the length of the string in the cell using the LEN function. Then it uses SUBSTITUTE to remove all the "a" characters from the string. The length of this new string is subtracted from the original length. The result is the number of occurrences of the character "a."