Formula doesn't work when indirectly referencing a range of cells from concatenate formula

I have a cell (say H6) containing concatenated information to result in a range (for example, "A1:C11"). If I wanted to sum up this range and typed it manually as =sum(A1:C11) it works, but if I wanted to refer to the same information but in a concatenated form that's in cell H6, I cannot. The formula I use is =sum(indirect("H6")).

I tried copying cell H6 and pasting the value into cell I6, and copying that value from the formula bar back into the summation cell, it works.

How can I reference the contents of cell H6 so that when it's plugged into the sum formula it'll work?

You don't need quotes around the H6, so do it like this:

=SUM(INDIRECT(H6))

Hope this helps.

Pete

Originally Posted by Pete_UK

You don't need quotes around the H6, so do it like this:

=SUM(INDIRECT(H6))

Hope this helps.

Pete

Wow! Thank you so much! Is there a reason why I do need the quotations when I type in =indirect function in a cell but don't need them in this case?

Originally Posted by FDibbins

Or, combined...
=SUM(INDIRECT(G3&H3&":"&G4&H4))

Thank you, this worked also! And I can skip having another cell displaying the concatenate value, too! Thank you so much!

INDIRECT expects a text parameter which represents a range. In your case, you already had that in H6, so you can refer to H6 directly in the formula, but you could do it like this:

=SUM(INDIRECT("A1:C11"))

where that range has to be within quotes, or you can do it like Ford has suggested and miss out the intermediate cell value.

Hope this helps.

Pete

Originally Posted by FDibbins

Or, combined...
=SUM(INDIRECT(G3&H3&":"&G4&H4))

What if G3:H4 were on sheet 2 and the data it's referring to be summed is still on sheet 1? What would this formula look like then?

You would have to have the sheet reference in front of each cell reference, like this:

=SUM(INDIRECT(Sheet2!G3&Sheet2!H3&":"&Sheet2!G4&Sheet2!H4))

Suppose, though, that the range you want to sum is actually on Sheet3, with those cells on Sheet2 containing the references - then you would have this:

=SUM(INDIRECT("'Sheet3'!"&Sheet2!G3&Sheet2!H3&":"&Sheet2!G4&Sheet2!H4))

Here the Sheet3 reference has to be within quotes (and it's always safer to put the apostrophes in), as that forms part of the final range reference that you want INDIRECT to act upon.

Hope this helps.

Pete

Originally Posted by Pete_UK

You would have to have the sheet reference in front of each cell reference, like this:

=SUM(INDIRECT(Sheet2!G3&Sheet2!H3&":"&Sheet2!G4&Sheet2!H4))

Suppose, though, that the range you want to sum is actually on Sheet3, with those cells on Sheet2 containing the references - then you would have this:

=SUM(INDIRECT("'Sheet3'!"&Sheet2!G3&Sheet2!H3&":"&Sheet2!G4&Sheet2!H4))

Here the Sheet3 reference has to be within quotes (and it's always safer to put the apostrophes in), as that forms part of the final range reference that you want INDIRECT to act upon.

Hope this helps.

Pete

This worked perfectly, thank you so much for helping me out!