Sum results of concatenate formula

[efx888]

I can’t for the life of me figure this out; neither on my own nor through hours of googling. Here’s the deal:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
I have a column of numbers I want to sum (or sumif.) These numbers are the result of several other functions. I THINK the root of the problem is that the numbers are pulled from the results of a CONCATENATE function (I used this to nest 17 IF functions.) I think that’s the root of the problem because I can’t seem to change the format to a number. (I changed it in the ‘format cells’ window, but none of the number styles do anything which would indicate a successful format change.) I’ve also used the LEFT and LEN functions to try to eliminate any non-number characters that, from what I’ve read, can be ‘non-printable?’ and can result from concatenating cells.
Here’s the part that’s really confusing though – If I manually write in an equation to sum two values resulting from the concatenate function, for example, A1+A2, then it gives me the sum, but, again, the formula =SUM(A1:A2) just gives me 0. <o></o>
<o> </o>
I hope this description of my problem makes sense. It barely does to me. Any help would be great. Thanks.<o></o>

 

[Jonmo1]

Welcome to the board...

Ideally, I would re-evaluate your concatenated 17 nested IF formula.
There's likely a better way...
Post that formula if you want to try to improve it.

That said, try just adding 0 to the end of your concatenate formula

=CONCATENATE(...,...)+0

That should make them actual numbers instead of "number stored as text"


Hope that helps.

 

[efx888]

Adding +0 worked. I was overjoyed. It was like Christmas morning when I saw that comma seperator pop up. Thank you so much.
As you mentioned, yes, I'm sure there is a better way to do it. All I really wanted to do was nest 17 IF functions, but as far as I know, you can only nest 7(?)
This forum has been a great help, even before I became a member. I'll certainly have more questions in the future, so I'm glad I have this resource.

 

[Jonmo1]

Glad to help...

Still would like to see your 17 nested if formula...
I'm 99% certain there's a better/simpler way to accomplish the same goal.

 

[Scott Huish]

Usually if you need that many IF functions there is usually a better way. I rarely use 3, let alone any more than that.

A lot of times a VLOOKUP or LOOKUP function is better suited.

 

[efx888]

Here is the formula:
=CONCATENATE(IF(A6=1,O$6,""),IF(A6=2,O$7,""),IF(A6=3,O$8,""),IF(A6=4,O$9,""),IF(A6=5,O$10,""),IF(A6=6,O$11,""),IF(A6=7,O$12,""),IF(A6=8,O$13,""),IF(A6=9,O$14,""),IF(A6=10,O$15,""),IF(A6=11,O$16,""),IF(A6=12,O$17,""),IF(A6=13,O$18,""),IF(A6=14,O$19,""),IF(A6=15,O$20,""),IF(A6=16,O$21,""),IF(A6=17,O$22,""))+0

Column "O" which, as you can see, is delivering the "TRUE" results of the IF functions is just a table of values.
Basically, it's saying, "If the zone number is 'x', then the footage is 'y'" and so forth.

 

[Jonmo1]

Try replacing all that with

=INDEX(O$6:O$22,A6)