Ampersand use within formulas.

[Bush32]

Can someone please explain why this formula doesn't work? "=SUM(D&A1&":D"&A2)" Where A1 and A2 equal numerical values that can be changed based on the users sum range desire.

Thanks!

 

[Peter_SSs]

It is because you cannot just use text strings in the function like that. You can do it with INDIRECT like this ..
=SUM(INDIRECT("D"&A1&":D"&A2))
.. but note that INDIRECT is a volatile function so can slow your sheet if used a lot.

A non-volatile formula to do the same job would be
=SUM(INDEX(D:D,A1):INDEX(D:D,A2))

Another advantage of this non-volatile formula is that if any new column(s) are inserted to the left of column D, it will still work whereas the INDIRECT formula would have to be manually changed to reflect the new column letter.

 

[Ells_]

When using the ampersand in cell references, you need to use the INDIRECT function. It's probably one of the most powerful built-in functions in Excel

say you have A1:A10 being equal to 1:10. If cell B1 has a value of 5, the correct way to reference A & B1 would be =INDIRECT("A"&B1), resulting in a value of 5

To specifically answer your question:
=SUM(INDIRECT("D"&A1&":D"&A2))

more reading on INDIRECT if you'd like: 403 Forbidden

 

[Beyond_avarice]

Ampersands infer that any data you have will be converted to a text string. A SUM formula will not work for Text values. It appears you are attempting to append actual values to the letter "D". I presume A1 and A2 posess some value that you are trying to use to get the SUM(D#:D#). I'm not sure why you don't just address those cells.

But try this:

=SUM(INDIRECT("D" & A1 & ":D" & A2))

 

[Ells_]

A non-volatile formula to do the same job would be
=SUM(INDEX(D:D,A1):INDEX(D:D,A2))

Woah, I had no idea INDEX actually returns addresses and not values. That blew my mind. Good to know

 

[Bush32]

Thank you all! These are just the answers I was looking for. I'm particularly intrigued but the use of the Index formula as well.

 

[Peter_SSs]

Thank you all! These are just the answers I was looking for. I'm particularly intrigued but the use of the Index formula as well.

You are welcome. If it was my sheet, I would definitely choose the INDEX structure for this job.

 

[Peter_SSs]

Woah, I had no idea INDEX actually returns addresses and not values.

It doesn't always do that of course, but Excel is smart enough to work out that is what we want in this case.