Excel function: reference to array in cell

Question

I have an array in cell A1 via

A1 = {=G6:J6} = {"aa"."b"."ccc".1}

Now I want to use the cell A1 for array formula in B1. Basically B1 should be

B1 = SUMPRODUKT((C6:C12)*(B6:B12=G6:J6))

But instead of the direct reference to G6:J6 I would like to use A1 instead. I just tried:

B1 = SUMPRODUKT((C6:C12)*(B6:B12=A1))
B1 =  {=SUMPRODUKT((C6:C12)*(B6:B12=A1))}

But this would not work. Is there a way to make it work?

Greetings, Peter

For questions that appeared:

Cells G6:J6 are input data for example article numbers. I want to setup the input data only once in my sheet so I have to update less data. entries in G6:J6 are strings or numbers. Let's say G6 = "aa", H6 = "b", I6 = "ccc" and J6 = 1.

Cell B1 is one point where I need to use the data. It would rather be in another sheet but for simpler examples let's assume it is cell B1. In B1 I could of course refer to G6:J6 but this makes formular less easy to read. Therefore I would like to put a reference A1 next to B1 so one can see easily what data B1 uses.

C6:C12 is some numbers and B6:B12 is some strings/numbers that maybe match G6:J6. So sumproduct should sumup the matches.

Can you provide a sample sheet and expected results? from your question, its not clear what you are trying to achieve — Zac
– Zac, Commented Feb 26, 2018 at 10:35

Charles Williams · Accepted Answer · 2018-02-26 11:04:37Z

0

Your cell A1 contains an array formula or array range but it only contains a single value from that array or range (each Excel cell can only contain a single result value).

So you need to replace the A1 in your SUMPRODUCT with an array or range expression.

answered Feb 26, 2018 at 11:04

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Frey Over a year ago

Is there any workaround? Or must I always refer to the original array?

Charles Williams Over a year ago

There are no simple workarounds: best answer is always to use the original array/range

shrivallabha.redij · Accepted Answer · 2018-02-26 12:21:34Z

0

Cell A1 value shall be G6:J6

G6:J6 populated as required with {"aa","bb","ccc",1} then in B1 put following formula and check if this is what you need.

=SUMPRODUCT(C6:C12*ISNUMBER(SEARCH(B6:B12,INDIRECT(A1))))

answered Feb 26, 2018 at 12:21

shrivallabha.redij

5,9021 gold badge15 silver badges28 bronze badges

1 Comment

Peter Frey Over a year ago

Hi, I will check if this gives me the result. However, I think it will be best to simply refer to the original range. It must be much faster than your result.

Collectives™ on Stack Overflow

Excel function: reference to array in cell

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related