Sum results of concatenate formula

efx888

New Member
Joined
Jun 13, 2011
Messages
3
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
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:p></o:p>

<o:p> </o:p>
I hope this description of my problem makes sense. It barely does to me. Any help would be great. Thanks.<o:p></o:p>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Jun 13, 2011
Messages
3
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
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

New Member
Joined
Jun 13, 2011
Messages
3
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.
 

Forum statistics

Threads
1,081,860
Messages
5,361,737
Members
400,653
Latest member
ProParadox

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top