Without googling it... #2

[lovingboth]

COUNTIF is a good first solution, although why on earth COUNTIF is not grouped with COUNT, or part of its see also is beyond me.

(Similarly with the "count unique entries in a list" help entry - to me, that's a different task (either count how many different items appear or how many appear only once) to the one it actually helps with (count how many times something appears)!)

What I got from googling, was a MS knowledge base article which copes with substrings and has similar examples that count the number of times a particular character is used or how many 'words' there are.

The "Formula to Count the Number of Occurrences of a Text String in a Range" they give is

=SUM(LEN(range)-LEN(SUBSTITUTE(range,"text","")))/LEN("text")

where range is the cell range in question and "text" is replaced by the specific text string that you want to count. It's a somewhat convoluted method but this does mean that "text, text" in a cell gets (correctly) counted as two occurrences, not one.

Of course, when you paste it into a cell, 'it don't work'.

I will confess to never having heard of these before, but apparently, you have to enter this as an array formula, using CTRL+SHIFT+ENTER. This adds some nice curly brackets around it... and gets the right result.

Comments

[tempaccount99]

Heh, I actually rather admire that solution, it's a pretty inventive way to solve the problem.