Why doesn't COUNTIF count items that are part of longer text in Excel?

COUNTIF matches the entire cell content by default. If 'Dog' is embedded in a sentence like 'The Dog ran', COUNTIF with exact criteria will miss it unless wildcards are used.

What array formula counts a word embedded in cell text across a range?

Use =SUM(LEN(range)-LEN(SUBSTITUTE(range, 'word', '')))/LEN('word') entered with Ctrl+Shift+Enter. It counts every instance of the word across all cells in the range.

How do I count cells that contain a specific word anywhere in the cell?

Use =COUNTIF(range, '*word*') with wildcards to count cells where the word appears anywhere, even if surrounded by other text.

Count a String Inside Multi-Text Cells in Excel

It is generally known that =COUNTIF() is the go-to function for counting the number of instances an item appears on a list, as demonstrated below: However, what if you are looking for an item that is ...

It is generally known that =COUNTIF() is the go-to function for counting the number of instances an item appears on a list, as demonstrated below:

However, what if you are looking for an item that is not the only content in its cell, like a word within a sentence? The =COUNTIF() function will not work as well, as it functions based on the premise that the searched for item is the only content in its cell.

This formula counts just 1 instance of "Dog" - in A2. But what about the instances in A4 and A5?

It could even skip that item if there is an extra space or carriage line in the cell - and that creates its own headache as these are not readily visible to the human eye (for help cleaning data with extra spaces and non-printing characters, see the Excel data clean-up function guide).

So, how do you then count items that are not the only content in their cells? There is an array formula that is here to help.

One way to think about array formulas is by the value they deliver. If we wanted to conduct the above search using a non-array formula, we would need to create a new column of formulae beside the data of interest that would search each cell for the value they were looking for (using =FIND(), =SEARCH(), or even =VLOOKUP()), and then sum up all the findings to get our results.

This approach utilizes too much physical and memory space, and could get tedious if the search was for more than one item.

Array formulas allow for compact calculation of values and help manage the excel real estate by completing within one cell what could take tens or hundreds of cells to do.

For the example above, the array formula below does the magic

=SUM(LEN($A$2:$A$9)-LEN(SUBSTITUTE($A$2:$A$9,”Dog","")))/LEN(“Dog")

Array formulas (also known as CSE formulas) are activated by pressing a combination of Ctrl + Shift + Enter (for regular formulas, you will only need to press Enter) after entering the formula into the cell.

You can tell when an array formula is active in a cell by the curly braces that surround the formula when viewed from the formula bar.

If you need to update the formula after entering it, step into it as you would do for any other formula, but remember to press Ctrl + Shift + Enter once done.

Count a Word or String Inside Cells That Contain Other Text in Excel

Frequently Asked Questions