Excel formula to count number of occurrences of a particular word in a Range

← Prev

You can use the COUNTIF() function in Excel to count number of occurrences of a particular word in a range.

Let us assume, I have a list of different types of birds in the 2nd column in my Excel worksheet. I want to know how many times (the number of times) a bird of type "Dove" or "Sparrow" is entered in the list.

The formula in this example uses the COUNTIF() function.

Syntax COUNTIF()

=COUNTIF(range, criteria)

The COUNTIF() function will count the number of cells that meets as certain criteria.

The function takes two parameters (arguments).

1) range: The cells you want to count. It can be a named range. This is required.

2) criteria: An "expression" or cell reference or a condition that will determine which cells to be counted. This is required.

The Formula

Assuming, I want to count number of "Sparrows" (bird type) in a range.

Select the cell where you want to get the result and type or copy and paste the below formula.


formula to count number of times a particular words is used in a range

The 1st argument is the range or the number cells I want the formula to count.

The 2nd argument is the criteria, where I am using a wildcard character "asterix" (*) as prefix and suffix and a "string" value (the bird type) in between. ("*Sparrow*")

It will return the number of times (cell count) the word is used in that range.

➡️ Wildcards: In Excel, wildcards are special characters (like the asterix) that are used as comparison criteria for text filters. Excel has three wildcard characters: asterix (*), question mark (?) and tilde (~).

← Previous