Sum of Named Ranges to Array in Excel

Question

I have values 1,2,3 and 2,3,4 in columes A and B respectively. I want column C to be 1+2, 2+3, 3+4. I have named the first 3 cells of column A as RANGE_A and the first 3 cells of column B as RANGE_B

I have tried sum(RANGE_A, RANGE_B), but that gives me the actual total of 15 in every cell of the output range.

I don't want to do this in VBA, and it would be cleaner if I can use the ranges like I have tried, but if all else fails i'll be just using A1+B1,A2+B2 etc..

***** EDIT ********************************
Where you want to sum two named ranges ={sum(RANGE_A, RANGE_B)} produces a scalar value, reflected in every cell of the output array, equal to the sum of both columns.

My solution is in fact, incredibly simple (thanks to QHarr, who got this right even when my original question was wrongly written!)

={RANGE_A + RANGE_B} produces an output array where each value is the sum of each pair of cells in each range. That's all i was looking for!

Cheers
J

I don't understand why you wouldn't just use =A1*B1 in C1, and fill down...? — Olly
– Olly, Commented Aug 30, 2018 at 8:38
Hi Olly, for presentation purposes. The users of the tool will benefit from seeing a named range which has a name they can relate to. A1*B1 requires investigation to understand what the cell's value means — John Owens
– John Owens, Commented Aug 31, 2018 at 12:53
Then why not use a listobject table - then the formula will use structured references - something like: =[@[Data Item A]]*[@[Data Item B]] — Olly
– Olly, Commented Aug 31, 2018 at 13:00

QHarr · Accepted Answer · 2018-08-31 13:30:21Z

5

You want to select C1:C3 and enter the following in the formula bar:

=RANGE_A*RANGE_B

then press Ctrl + Shift + Enter to enter as an array formula.

For addition you can use:

=RANGE_A+RANGE_B

edited Aug 31, 2018 at 13:30

answered Aug 30, 2018 at 8:39

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

2 Comments

John Owens Over a year ago

QHarr, i made a right balls up of my post. It should have a +, not a *.

QHarr Over a year ago

Simply change to a + :-)

Collectives™ on Stack Overflow

Sum of Named Ranges to Array in Excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related