Home |
Search |
Today's Posts |
#1
|
|||
|
|||
how to sum up letters instead of numbers?
I can use formulas to calculate numbers or figures. How do I sum up letters
in a range of cells? |
#2
|
|||
|
|||
Care to give an example that shows what you want?
"Iviio" wrote in message ... I can use formulas to calculate numbers or figures. How do I sum up letters in a range of cells? |
#3
|
|||
|
|||
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) "Iviio" wrote in message ... I can use formulas to calculate numbers or figures. How do I sum up letters in a range of cells? |
#4
|
|||
|
|||
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 "Iviio" wrote in message ... I can use formulas to calculate numbers or figures. How do I sum up letters in a range of cells? |
#5
|
|||
|
|||
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 "KL" <lapink2000(at)hotmail.com wrote in message ... 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 "Iviio" wrote in message ... I can use formulas to calculate numbers or figures. How do I sum up letters in a range of cells? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) | |||
Sorting imported "numbers" | Excel Discussion (Misc queries) | |||
row numbers & column letters | Excel Discussion (Misc queries) | |||
How do I change column labels from numbers to letters in Excel? | Excel Discussion (Misc queries) |