Sum of columns whose header appears in a lookup table

So i have the main data table:

ColA   ColB   ColC   ColD
aa      1       0     1
bb      1       2     2
cc      1       2     3

Row aa, bb, cc, etc. The total number of rows shouldn't be larger than a couple of hundred .

A second sheet\collumn has a table that list only the relevant column's helper, adaptable on the fly:

Helper
ColB
ColD

The search helper tells the functions which columns i should use in the search.

The idea is to sum all values from columns that are refered in the Helper table and that match the unique identifier in "ColA".

So the result would, using the above Helper table:

• Identifier aa,开发者_运维技巧 would return 2.
• Identifier BB, would return 3.
• Identifier CC, would return 4.

Any idea how to do it.

You can do this with VLOOKUP, IF and SUM:

IF(VLOOKUP(C1,$H1:$H99,1)=C1, SUM(C2:C99), "")

Enter this formula with Control+Shift+Enter to make it an array formula.

=SUM(($A$2:$A$4="aa")*((($B$2:$B$4)*NOT(ISNA(MATCH($B$1,rngHelper,FALSE))))+(($C$2:$C$4)*NOT(ISNA(MATCH($C$1,rngHelper,FALSE))))+(($D$2:$D$4)*NOT(ISNA(MATCH($D$1,rngHelper,FALSE))))))