Multiple lookup criteria and sum values based on matches.

Hi There,

Following is the scenario i ran into:

I have input sheet with several combinations entered by user as shown below:

A1 A2 A3
A X S
M F T
C D F
N C D
Y A
Z

where A1, A2, and A3 are headers/range with values as header names of columns used in sheet 2.

In sheet 2, i have around 100 rows with data entered (all numbers) for column headers A, B, C, D...Z. Now, i will ask user to enter the Combination (as defined in input sheet) after the last used column, after entering the required combination, i would like to add the values for that combination. By that i mean, say if user selected A1, then i would like to add the row values of columns A, M, C, N, Y, Z as defined in input sheet.

I played around with using SumIFS and other array functions but couldnot get it to work.

Any help would be greatly appreciated.

Thanks in advance and please let me know if further clarification is required.

Sat.

Try this...
=SUMPRODUCT(C$2:AB$2,--($C$1:$AB$1=Sheet1!A2)+($C$1:$AB$1=Sheet1!A3)+($C$1:$AB$1=Sheet1!A4)+($C$1:$AB$1=Sheet1!A5)+($C$1:$AB$1=Sheet1!A6)+($C$1:$AB$1=Sheet1!A7))
copied down and across

edit: JeteMc just pointed out some incorrect absoluting in my formula - thanks for that. It should have been...
=SUMPRODUCT($C2:$AB2,--($C$1:$AB$1=Sheet1!A$2)+($C$1:$AB$1=Sheet1!A$3)+($C$1:$AB$1=Sheet1!A$4)+($C$1:$AB$1=Sheet1!A$5)+($C$1:$AB$1=Sheet1!A$6)+($C$1:$AB$1=Sheet1!A$7))

Nice 1 Jonmo

This ARRAY formula also works...
=SUM(SUMIF($C$1:$AB$1,Sheet1!A$2:A$7,$C2:$AB2))
entered using CTRL SHFT ENTER

Thanks for the feedback