Search code examples
vbaexcelnamed-ranges

Sum of Named Ranges to Array in Excel

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

Solution

  • 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