CODEWITHSUNDEEP
excelexcel-formula
L.P.
L.P.

Reputation: 83

Counting Characters in Excel

I am trying to count the occurrences of certain character within a range. I.E. If I have several cells with words in them I want to count the number of times Capital letter "A" appears.

The most common answer I find (even on this site) is 

   =SUM(LEN(F59:F73)-LEN(SUBSTITUTE(F59:F73,"A","")))

This particular formula only seems to count the first cell in the range and no others. Why is this happening when it seems to be quite the universal answer?

Upvotes: 2

Views: 101

Answers (1)

tigeravatar
tigeravatar

Reputation: 26650

You either need to array enter the formula using Ctrl Shift Enter, or change the SUM to be a SUMPRODUCT to force array calculation without having to array enter the formula:

=SUMPRODUCT(LEN(F59:F73)-LEN(SUBSTITUTE(F59:F73,"A","")))

Upvotes: 4

Related Questions