how to sum up letters instead of numbers?

[Guest]

I can use formulas to calculate numbers or figures. How do I sum up letters
in a range of cells?

 

[Aladin Akyurek]

Care to give an example that shows what you want?

 

[Bob Phillips]

Do you mean count the instances of a letter, such as

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[KL]

Iviio,

Try this:

=SUMPRODUCT(LEN(A1:A10))-SUMPRODUCT((LEN(A1:A10)<>LEN(SUBSTITUTE(A1:A10,{"
",0,1,2,3,4,5,6,7,8,9},"")))*(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,{"
",0,1,2,3,4,5,6,7,8,9},""))))

or this

=SUMPRODUCT((LEN(A1:A10)<>LEN(SUBSTITUTE(UPPER(A1:A10),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},"")))*(LEN(A1:A10)-LEN(SUBSTITUTE(UPPER(A1:A10),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},""))))

Regards,
KL

 

[KL]

This array formula (Ctrl+Shift+Enter) also seems to work provided the first
cell (A1) is not empty and no cells in the range contain error value:

=SUM(LEN(A1:A10))-SUM(IF(ISERROR(--(MID(A1:A10,COLUMN(INDIRECT("1:"&LEN(A1:A10))),1))),0,1))

Regards,
KL