Sum different alphabets in an excel range.

Solved/Closed
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015 - Sep 18, 2015 at 12:34 PM
Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 - Sep 20, 2015 at 11:08 AM
some one please help me......how to sum different alphabets in an excel range..........example.....range is ...A!:Z1 and i want to sum "P" and C in range.

5 replies

Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 146
Sep 20, 2015 at 11:08 AM
tahir147, Good afternoon.

I feel happy to help you.

I wish you a good week of work full of health and happiness to you and your whole family.

Best regards!
--
Belo Horizonte, Brasil.
Marcílio Lobão
1
Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 146
Sep 18, 2015 at 02:32 PM
tahir147, Good afternoon.

Your request is very confused.

Initially, you can not add letters, only numbers.
You can COUNT letters if this is the case.

Please, explain with examples what do you want to do.
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 19, 2015 at 10:21 AM
Good Evening .Sir,Mazzaropi.
I want to creat a table for my office work ......its contents like this

thirty or thirty one colums by monthwise......i put in every cell "P" "C" "D" or "CP"
and i want to count after the month that how many "P" "C" "D" or "CP" I entered in the whole month.....and if "CP" i entered in one cell its shows in total sepretly with C....and ,..P


I hope you understand please hlelp me.....
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 19, 2015 at 10:34 AM
if you can,t understan pleas send me you email i will send you preapred table with exemle in excel file.
0
Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 146
Sep 19, 2015 at 10:57 AM
tahir147, Good morning.

Now your explanation make sense for me, thanks.

Suppose:
A1:AE1 --> 31 days of month
A2:AE2 --> YOUR DATA

To COUNT how many times a letter appears you can use:
=COUNTIF($A$2:$AE$2,"C")

I did an example for you:
http://speedy.sh/fp8GN/19-09-2015-Example-tahir147.xlsx

"...and if "CP" i entered in one cell its shows in total sepretly with C....and ,..P ..."

This part I can't understand what you need.

Take a look at my example and tell us if it worked for you.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 19, 2015 at 12:27 PM
Thanks Mazzaropi Sir,

its good but one little thinks that I can,t adopt this function that for example if i entered in a range cell CP or CD its dos,t count with C , P, and D..
i want to count all total no of letters although if in one cell have more then one letter.

thanks for reply, you can solve my problem..
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 19, 2015 at 01:10 PM
I tell you compleet story,

Letters P,C,B,G,F are showing the cetogory of empty bags like

P= Plastic Bag ,C=Canves Bag ,B= Blue Bag ,G= Green BAg ,F= foreign Bag

thees bags are recived to my main branch from different sub offices daily in wohole month,
Countif funtion is useful when I recived only one bag from thees cetogoreis daily but for example if sub office send both C (canves bag) and P (plastic bag) in one day so then how i count this at the end of the month,

I hop you understand
0
Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 146
Sep 19, 2015 at 01:35 PM
tahir147, Good afternoon.

"...i want to count all total no of letters although if in one cell have more then one letter..."

In this case you can try this one:

Matricial Formula
=SUM(LEN(A2:AE2))
Press SHIFT + CTRL + ENTER at final

Is that what you're looking for?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 19, 2015 at 02:02 PM
=SUM(LEN(A2:AE2)) this funtion dos,t work for me
0

Didn't find the answer you are looking for?

Ask a question
Mazzaropi Posts 1967 Registration date Monday August 16, 2010 Status Contributor Last seen October 3, 2022 146
Sep 20, 2015 at 08:57 AM
thair147, Good morning.

With this new specification we will need a different formula.

Try to use:
For a specific CODE.
=SUMPRODUCT((LEN($A$2:$AE$2)-LEN(REPLACE($A$2:$AE$2;"C";""))))

For a CODE in a CELL.
=SUMPRODUCT((LEN($A$2:$AE$2)-LEN(REPLACE($A$2:$AE$2;J6;""))))

NEW model with a new formula:
http://speedy.sh/vJsCQ/20-09-2015-en-Kioskea-Counting-Codes-OK.xlsx

Please, do your tests and tell us if it worked for you.
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 20, 2015 at 10:30 AM
Good Evening Mazzaropi Sir,

I did same code but it give an eror showing full black highlighted in second part of fuction $AE$2 in above specific both codes.
0
tahir147 Posts 8 Registration date Friday September 18, 2015 Status Member Last seen September 20, 2015
Sep 20, 2015 at 10:50 AM
Thanks Sir, I got it ,

actually You use word REPLACE in above file instead of SUBSTITUTE.
whan i see fuction in excel file i got it and it work parfectly that i want....Now I can make my table for my office ....thanks allott sir ...Hope you wiil help me in future as well.....Good By
0