Count number of instances of a character in a column of cells

[mforbes6186]

Hi,

The second part of my previous question (http://www.excelforum.com/excel-gene...le-string.html) is how can I count the number of instances of a character in a column of cells?

eg.

Alpha
Bravo
Charlie
Delta
Echo

Answer

A = 5
B = 1
...
Z = 0

Again, ideally each letter will be calculated using a single formula without a macro. I only need an example for letter A, I can fill out the rest of the alphabet.

I appreciate all your help in advance!

[daddylonglegs]

For a count of "a" or "A" in A1:A10 try

=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(LOWER(A1:A10),"a","")))

[mforbes6186]

Great thanks, exactly what I needed!

[FDibbins]

Hi

I got this from 1 of the genius members on here (wish I remembered their name)

With your data in column A, create a list of the alphabet in a column (I used E), then next to the letters, use this, copied down...
=SUM((LEN($A$2:$A$101)-LEN(SUBSTITUTE(UPPER($A$2:$A$101),E2,""))))
it is an array formula and must be entered using CTRL SHIFT enter. not just enter