How to reference an array with indirect

[netgroover]

I want to use Indirect to reference and sum an array, for example {1,2,3,4,5}. If I type "=sum({1,2,3,4,5})" in a cell, I get the correct result which is 15. If I place "{1,2,3,4,5}" in cell A1, and then type "=sum(indirect(a1))" I get #REF. What is the correct way to reference the array - or what is the correct way to enter the array into cell A1? I've tried putting quotes around it, and entering the array with SHIFT+CTRL+ENTER but nothing seems to allow the INDIRECT function to properly pull the array in to be evaluated.

Thanks to anyone who can help me! I'm new to the forum.

[Tony Valko]

The INDIRECT function argument must resolve to a valid reference. The array constant is not a valid reference.

If you were to put in cell A1:

A1:A5 (or 1:5)

Then this array formula** will give you the sum of 1,2,3,4,5:

=SUM(ROW(INDIRECT(A1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FDibbins]

Hi and welcome to the forum

To add in to what Tony just said, if you meant this literally....

If I place "{1,2,3,4,5}" in cell A1

This will give you a text entry of "{1,2,3,4,5}" in cell A1, and unless you break that apart it will always be text - and you cant add/sum text

[Tony Valko]

Another option is to use a VBA UDF (User Defined Function) that uses the Evaluate function.

Please Login or Register  to view this content.

Copy the code and paste it into a general module.

Then, you could enter the array like this:

A1 = 1,2,3,4,5

And the formula would be:

=SUM(EVAL(SUBSTITUTE(A1,",","+")))

Or, like this:

A1 = 1+2+3+4+5

And the formula would be:

=EVAL(A1)

[netgroover]

Ok, thanks a bunch for answering my post.

I should have posted my entire formula. I'm using SUMIFS to sum values in column B based on the values in column C. Column C has values 1 to 7. I want to have a cell, NAMEed "key" which can dictate which values 1,2,3,4,5,6 or 7 are to be included in the SUM, and thereby make the SUMIFS function dynamic. For example, without trying to reference the values from a cell, SUM(SUMIFS(B:B, C:C, {1,2,3,4,5})) will sum values in col B if the value in col C is 1,2,3,4 or 5. I want to be able to change the values in named range "key" to control the output of the formula. How can I replace the {1,2,3,4,5} with a reference to the "key" range so I can change the key to {3,4} for example and not have to rewrite the formula?

Thanks again for your help!

[Tony Valko]

Use separate cells for the criteria.

If this is your data in the range B2:C10...

19.....3
63.....6
80.....2
95.....2
23.....4
56.....5
95.....5
20.....1
25.....2

Then, you can enter the number criteria in the range D2:D8...

19.....3.....1
63.....6.....2
80.....2.....3
95.....2.....4
23.....4
56.....5
95.....5
20.....1
25.....2

Meaning: Sum column B where column C = 1 or 2 or 3 or 4.

=SUMPRODUCT(SUMIF(C2:C10,D2:D8,B2:B10))

[netgroover]

Tony, thanks!

It works, although I can't wrap my brain around what SUMPRODUCT does... I understand SUMIF logic, but don't understand how that function works as an argument to SUMPRODUCT. I was able to name the criteria ranges and us Indirect in the formula, so I really appreciate your taking the time to fix my dilemma.

[Tony Valko]

Good deal!

For more on SUMPRODUCT see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html