Sum different alphabets in an excel range. [Solved/Closed]

Report
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015
-
Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
-
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

Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
136
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
136
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.
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

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.....
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

if you can,t understan pleas send me you email i will send you preapred table with exemle in excel file.
Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
136
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
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

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..
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

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
Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
136
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
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

=SUM(LEN(A2:AE2)) this funtion dos,t work for me
Posts
1842
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
October 27, 2020
136
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
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

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.
Posts
8
Registration date
Friday September 18, 2015
Status
Member
Last seen
September 20, 2015

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