(Short tutorial alert)

If you needed to count the number of times a certain string appeared in a range, the COUNTIF() function would be able to do that, seamlessly. But what happens when there are other text alongside that string in the cells in the range?

The COUNTIF function works with the assumption that the string or value in focus is the only one in the cell, if there are others within the cell it automatically excludes them.

To count the times a string appears in a range regardless of other text in the cells, this formula below comes in handy.

=SUM( LEN( [range] ) – LEN( SUBSTITUTE( [range],[string],”” ) ) ) /LEN( [string] )

The above formula is an array formula, which means it is only activated using the combination of Ctrl+Shift+Enter, versus just Enter for regular formulas.

Using this array formula, you get the results below: