Totaling Concatenated Cells

[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

 

[Dave R.]

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.

 

[Biff]

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

 

[Dave R.]

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

 

[umpie]

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

 

[Dave R.]

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.

 

[Biff]

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