How to sum many INDEX/MATCH values given criteria?

Question

I'm working on a dataset (30,000 rows x 1,000 columns) that looks like:

    Alpha  Beta  Gamma
A     3      4     3
B     4      5     7
C     9      2     4
D     6      9     3

I need to add multiple selected values in this format:

Selected Values      Total Gamma     Total Alpha    ...etc.
B  C  D  A               17               22        ...etc.
C  A                      7               12        ...etc.
A  D  C                  10               18        ...etc.

I could add INDEX/MATCH results one by one and drag down:

= IFERROR((INDEX($A$1:$D$6, MATCH($A10, $A$1:$A$6, 0), MATCH(F$9, $A$1:$D$1, 0))), 0) 
+ IFERROR((INDEX($A$1:$D$6, MATCH($B10, $A$1:$A$6, 0), MATCH(F$9, $A$1:$D$1, 0))), 0)
+ so on for each selected value...

But as some rows have 500+ selected values (A, B, C ... ZZ), is there a simpler way to add all this?

Any help would be much appreciated!

Answer 1

It will likely take some time to calculate all the results because of the multiple criteria and size of the data, but I'd suggest SUMPRODUCT, the formula I'm using in H2 is:

=SUMPRODUCT($B$2:$D$5*($B$1:$D$1=H$1)*ISNUMBER(SEARCH($A$2:$A$5,$G2)))