Sum of Named Ranges to Array in Excel

1
votes

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

1
vbaexcelnamed-ranges
I don't understand why you wouldn't just use =A1*B1 in C1, and fill down...? - Olly
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
Then why not use a listobject table - then the formula will use structured references - something like: =[@[Data Item A]]*[@[Data Item B]] - Olly
equally good solution - John Owens

1 Answers

5
votes

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.

data

For addition you can use:

=RANGE_A+RANGE_B