CONCATENATE within a SUM forumla

[Pedro's PhD]

Hi,

I am using a simple =CONCATENATE("B",(RIGHT(C12,4)))

The result of this formula returns B3345 which is good, but i then want to integrate this into a simple SUM formula,

eg, =SUM(B3345:B6000)

How can i integrate the CONCATENATE and SUM formula into one?

Thanks

Pedro

 

[T. Valko]

Hi,

I am using a simple =CONCATENATE("B",(RIGHT(C12,4)))

The result of this formula returns B3345 which is good, but i then want to integrate this into a simple SUM formula,

eg, =SUM(B3345:B6000)

How can i integrate the CONCATENATE and SUM formula into one?

Thanks

Pedro

Try this...

Let's assume A1 = B3345

=SUM(INDIRECT(A1):B6000)

 

[T. Valko]

Hi,

I am using a simple =CONCATENATE("B",(RIGHT(C12,4)))

The result of this formula returns B3345 which is good, but i then want to integrate this into a simple SUM formula,

eg, =SUM(B3345:B6000)

How can i integrate the CONCATENATE and SUM formula into one?

Thanks

Pedro

Try this...

Let's assume A1 = B3345

=SUM(INDIRECT(A1):B6000)

Or, you can do the concatenation directly in the formula like this...

=SUM(INDIRECT("B"&RIGHT(C12,4)):B6000)

 

[Pedro's PhD]

Hi,

Excellent, thanks very much!

Pedro

 

[Rick Rothstein]

First off, you do not need to use the CONCATENATE function, just link your items together with an ampersand. Your posted CONCATENATE formula can be written like this...

="B"&RIGHT(C12,4)

As for your SUM formula, you need to use the INDIRECT formual to from your range so that Excel sees it as a range and not text. You can either create the entire range in text and then apply the INDIRECT function to that...

=SUM(INDIRECT("B"&RIGHT(C12,4)&":B6000"))

or you can restrict the INDIRECT function to just the text you are forming...

=SUM(INDIRECT("B"&RIGHT(C12,4)):B6000)

In the first case, the B6000 will never change if you insert/delete rows or columns or if you copy/move the formula because that address is formed from text and Excel doesn't see it until the INDIRECT function evaluates it. In the second case, the B6000 is a real Excel range and will change like any other range reference when rows or columns are inserte/deleted or the cell is copied, etc.

 

[Weaver]

=SUM(INDIRECT(A1):B6000)

I'm surprised this works - wouldn't have expected that syntax to be supported, to the point where I've spent time coming up with workarounds!

 

[T. Valko]

Hi,

Excellent, thanks very much!

Pedro

You're welcome. Thanks for the feedback!

And just for the heck of it, here's another one...

=SUM(INDEX(B:B,RIGHT(C12,4)):B6000)

 

[Pedro's PhD]

First off, you do not need to use the CONCATENATE function, just link your items together with an ampersand. Your posted CONCATENATE formula can be written like this...

="B"&RIGHT(C12,4)

As for your SUM formula, you need to use the INDIRECT formual to from your range so that Excel sees it as a range and not text. You can either create the entire range in text and then apply the INDIRECT function to that...

=SUM(INDIRECT("B"&RIGHT(C12,4)&":B6000"))

or you can restrict the INDIRECT function to just the text you are forming...

=SUM(INDIRECT("B"&RIGHT(C12,4)):B6000)

In the first case, the B6000 will never change if you insert/delete rows or columns or if you copy/move the formula because that address is formed from text and Excel doesn't see it until the INDIRECT function evaluates it. In the second case, the B6000 is a real Excel range and will change like any other range reference when rows or columns are inserte/deleted or the cell is copied, etc.

Thanks Rick, very helpful explanation.