Trying to sum a column that contains various text

[Solved/Closed]
Report
Posts
3
Registration date
Wednesday January 14, 2015
Status
Member
Last seen
January 15, 2015
-
Posts
3
Registration date
Wednesday January 14, 2015
Status
Member
Last seen
January 15, 2015
-
I'm a novice when it comes to excel and I'm hoping someone can help me with a formula. I'm trying to give a value to various text in columns and then sum the column. For example, column B has 15 rows. Each cell in the column contains various text that reads "BA1,DK1" or "BA2,JN2". Each number after the text would change depending on what row it is in. How would I give a value of "1" to each text of "BA1,DK1,BA2,JN2,etc.." so when I add or remove one from the column my sum reflects the addition or subtraction of said text group?

1 reply

Posts
3
Registration date
Wednesday January 14, 2015
Status
Member
Last seen
January 15, 2015
1
OK. After playing around with it I'm most of the way to figuring it out. I gave everything in the cell a value of 1 using the following formula:

=SUMIF(B4:B18,"*",W3:W3)

Now my only problem is if I have more than 1 text string in the same cell. This is an example of what i have:

BA1
JG2
CF3, DK3
DK4, MW4
TO5, DK5




BA10

BA12
BA13
BA14
BA15

I'm getting a sum of 10, but I need to be getting a sum of 13. The DK3, MW4, and DK5 aren't getting assigned a value of 1. The entire cell is just 1. How do I adjust this to get to 13?
1
Thank you

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

CCM 2821 users have said thank you to us this month

Posts
47368
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,033
Thank you Chris for letting us all know. You are getting very wise in Excel! Congratulations!
Posts
3
Registration date
Wednesday January 14, 2015
Status
Member
Last seen
January 15, 2015
1
Thanks, Ambucias. This isn't anything for work, just for a fantasy baseball league I play in and this is our way of keeping track of draft picks that have been traded. I've been trying to figure this out for a while now and I've probably spent way more time on it than I should've. LOL. Figured I'd let everyone know where I was at in case they were trying to come up with an answer for me.