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.

[a-musing-amazon.livejournal.com]

Array formulae are pretty cool - they very useful for producing tables of stats out of lists (Excel data tables), allowing you to do the equivalent of countif or sumif but with multiple conditions (such as matching the row border and the column border in a table you need to produce). You can also use them with more complicated functions such as medians.

[drdoug.livejournal.com]

I was being a bit simple-minded about the text matching and hadn't realised you wanted to count "text, text" as two occurences.

That solution is so awful it's brilliant. I wouldn't have even tried to do it in Excel - I'd have exported it all and used something else.

[a-musing-amazon.livejournal.com]

..however it does require that the text strings don't overlap - it would only have picked up one of the "text" in "textext".

[lovingboth]

I can see this ending in jokes about greedy bisexual matching...