Summing a Named Range when said name is being pulled from another cell

[newfrito11]

Hi all,

I would like to do the following:

Cell "A1" - Apples/Orange/Bananas (these are all named ranges in my workbook)

Cell "B1" = sum(range mentioned in cell "A1") <- currently all this does is return the sum of A1 which is 0 or #NAME.


Thoughts?

Thanks!

[Pete_UK]

If you only had one name in A1, then you could do this in B1:

=SUM(INDIRECT(A1))

However, it will need to be a lot more sophisticated if you have more than one name in A1 separated by /.

Hope this helps.

Pete

[newfrito11]

If you only had one name in A1, then you could do this in B1:

=SUM(INDIRECT(A1))

However, it will need to be a lot more sophisticated if you have more than one name in A1 separated by /.

Hope this helps.

Pete

Pete, your the best. I didn't even think to try that. Can't tell you how happy I am! Just out of curiosity, because it very well might come up in the future, how would i need to go about summing up multiple named ranges? A macro would take too long. I don't like lag.

[Pete_UK]

I would not advise it. Suppose you had two names in A1 separated by a /, then you could use this:

=SUM(INDIRECT(LEFT(A1,FIND("/",A1)-1))) + SUM(INDIRECT(RIGHT(A1,LEN(A1)-FIND("/",A1))))

With 3 names you would need 3 terms, with a LEFT, MID and RIGHT term to get the names, and if there were a variable number of names then you would need IFs to generate the correct sum - I suppose by "sophisticated" I really meant "complicated".

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[newfrito11]

I would not advise it. Suppose you had two names in A1 separated by a /, then you could use this:

=SUM(INDIRECT(LEFT(A1,FIND("/",A1)-1))) + SUM(INDIRECT(RIGHT(A1,LEN(A1)-FIND("/",A1))))

With 3 names you would need 3 terms, with a LEFT, MID and RIGHT term to get the names, and if there were a variable number of names then you would need IFs to generate the correct sum - I suppose by "sophisticated" I really meant "complicated".

Hope this helps.

Pete

P.S. If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

Are you not advising the formula? or using a macro to replicate the formula? I don't see any downfalls especially if I begin the formula with if(isnumber(search("*/*")),.....

I appreciate your help on this.

[Tony Valko]

Just for the challenge and the academic exercise...

A1:A5 = named range Apples
B1:B5 = named range Oranges

D1 = Apples, Oranges

To get the sum of the named ranges entered in D1:

=SUMPRODUCT(SUMIF(INDIRECT(INDEX(TRIM(MID(SUBSTITUTE(D1,", ",REPT(" ",99)),(99*(ROW(INDIRECT("1:"&LEN(D1)-LEN(SUBSTITUTE(D1,",",""))+1))-1))+1,99)),0)),">-1000000"))

[Tony Valko]

Is there a reason yours would work better than Pete's?

If there will only be a few ranges Pete's suggestion is better.

If there will be "many" ranges then I would put each range name in its own cell...

A1:A5 = named range Apples
B1:B5 = named range Oranges
D1:D2 = the range names

Data Range

	A	B	C	D	E
1	23	94		Apples	616
2	47	66		Oranges
3	90	86
4	57	37
5	75	41
6	------	------	------	------	------

This formula entered in E1:

=SUMPRODUCT(SUMIF(INDIRECT(D1:D2),">-1000000000"))

Quite a difference, eh?

The other formula was just to see if it could be done.

[newfrito11]

Tony,

Would you mind talking me through your formula? mainly the >-100000000 portion

[Tony Valko]

The >-100000000 is the SUMIF criteria.

We want a simple sum of the named ranges but the SUM function won't accept the array of named ranges so we have to use the SUMIF function as an alternative.

The SUMIF function requires a criteria so I arbitrarily used >-100000000 which means greater than negative 100 million. There's a pretty good chance that your numbers will meet that criteria.