(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))

enter image description here

Answered By – Tom Sharpe

Answer Checked By – Cary Denson (AngularFixing Admin)

Leave a Reply

Your email address will not be published.