Totaling Concatenated Cells

  • Thread starter Thread starter umpie
  • Start date Start date
U

umpie

I'm trying to CONCATENATE two cells containing TEXT and one cell
containing a VALUE. Then ADD up the CONCATENATED Values.


Cell A: Contains the text - SAT
Cell B: Contains the text - Win
Cell C: Contains the Number - 2
Cell D: Contains the Text - MON
Cell E: Contains the text - Tie
Cell F: Contains the Number - 1

Cell G: Contains the CONCATENATE of Cells A,B,C - SATWin2
Cell H: Contains the CONCATENATE of Cells D,E,F - MONTie1


Cell I: I'm trying to then count ONLY the numbers(2 & 1) from cells G
& H. So that the total in Cell I will equal 3
 
If the numbers are only 1 digit, 0-9, you can use
=SUM(RIGHT(G1,1),RIGHT(H1,1))

There are other, longer formulas to extract the numbers, any # of digits,
from the text string, you can google search for those.
 
Hi umpie,

Why not just sum cells C and F? But to answer your
question, try this:

=SUM(--RIGHT(G1,1),--RIGHT(H1,1))

The double "--" converts the *TEXT* numbers to numeric
numbers.

This will work for the example data that you supplied.
However, this is not very robust. Can the numbers be more
that a single digit? If so, that makes it a little more
complicated.

Biff
 
Biff, I have tested without, and the double negative isn't necessary to sum
the numbers, though I spose it doesn't hurt.

I am using Excel 2000.

Dave
 
The reason that I cannot just sum up the cells is because I want it to
count one cell if two others contain certin things. For example
If A2 = Mon
& B2 = Win
Then count C2
and so on.

Also the numbers will never be more then one digit.

Thanks for your help
 
Question still unclear to me. Do you want to place values one column IF
(what u wrote below)? if so thats an IF(AND(x,y)) statement. Also just
because you have a contancated cell doesn't mean you have to take the value
from off the end of the string, you can just sum/count/etc the value from
the original column, which I suggest would be easier.
 
Hi Dave,

Yes, you're correct. In this case, it's a bit of
redunancy. It's a habit of mine whenever I use functions
like LEFT, RIGHT, and MID to extract numbers that need to
be calculated. This eliminates alot of #VALUE errors.

Biff
 
Back
Top