Previous Topic Next Topic

Trying to sum a column that contains various text

Solved/Closed
chris82675 Posts 3 Registration date Wednesday January 14, 2015 Status Member Last seen January 15, 2015 - Jan 14, 2015 at 11:29 AM
chris82675 Posts 3 Registration date Wednesday January 14, 2015 Status Member Last seen January 15, 2015 - Jan 15, 2015 at 10:07 AM
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?
Related:

1 response

Answer 1 / 1
chris82675 Posts 3 Registration date Wednesday January 14, 2015 Status Member Last seen January 15, 2015 1
Jan 14, 2015 at 01:12 PM
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?
Ambucias Posts 47310 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,166
Jan 14, 2015 at 05:22 PM
Thank you Chris for letting us all know. You are getting very wise in Excel! Congratulations!
chris82675 Posts 3 Registration date Wednesday January 14, 2015 Status Member Last seen January 15, 2015 1
Jan 15, 2015 at 10:07 AM
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.

Similar discussions

Auto generate serial number in excel based on Column
Raj_1562 -
TrowaD -

4 responses
VBA find column by name and sort
SidK -
bOFILL -

4 responses
How Sum the Column value in Excel via VBA based on criteria?
Raj_2823 -
TrowaD -

2 responses
Converting rows of data in notepad to columns
Need help -
Philgood_FR -

2 responses