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

Question

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..

Answer 1

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