# (excel) How to return an array from a sum of ranges?

## Issue

I’m setting up a morphological table that will have to go through potentially a couple hundred items, so it’s desirable for this process to not be done by hand.

Here’s a small summary of the situation:

fin eng op fli
A 2 4 6 8
B 1 3 5 4
C 1 2 3 5
D 1 4 7 2

The first column holds named ranges A through D which have associated values from the 4 categories in row 1.
In a second table we create configurations based on which features are selected, something like this:

Config 1 Config 2
A B
C D

What I’m looking for is a formula that would read for each configuration which named range is selected, add the score for each category and return it in a simple array. Something like

Config 1 {3,6,9,13}, Config 2 {2,7,12,6}

So far I’ve found that the Indirect formula works exactly the way I want but I have to manually input each range. Something like:

``````=INDIRECT(A1)+INDIRECT(A2)
``````

I’ve played around with different permutations of sum functions but instead of returning the arrays it returns the sum of the first values.

``````=SUM(INDIRECT(A1:A2))
``````

Amy suggestion would be welcome.
I know this would probably be much simpler with code but this study needs to be done in excel..

## Solution

I’m not sure if this answers your question as it doesn’t use named ranges, but you could try something like this:

``````=MMULT(SEQUENCE(1,4,1,0),\$B\$2:\$E\$5*COUNTIF(INDEX(\$H\$2:\$I\$3,0,ROW()-ROW(\$A\$7)+1),\$A\$2:\$A\$5))
``````

Answered By – Tom Sharpe

Answer Checked By – Cary Denson (AngularFixing Admin)