SUM IFS Help pulling from table of info

I am using Excel 2007 and trying to use SumIFS to retrieve information as described below.


In Cell G2 of sheet one I am trying to pull the value for the work order number (Cell F1 Sheet 1) for the cost code in cell A2/Sheet 1.
I need retrieve the information from sheet two where the work order numbers are listed in row 2 and change from column to column by work order number.
Cell F1 of sheet one is a drop down list and the work order numbers can change depending on the selection.
G2 of sheet 1 needs to retrieve the amount by Cost Code from sheet two for that particular work order.

If the row and column headers on Sheet2 are unique, then try:

=INDEX(Sheet2!$B$3:$Q$128,MATCH($A2,Sheet2!$A$3:$A$128,0),MATCH($F$1,Sheet2!$B$2:$Q$2,0))

If there could be duplicate row or column headers and you want to sum all.. then:

=SUMPRODUCT(Sheet2!$B$3:$Q$128,(A2=Sheet2!$A$3:$A$128)*($F$1=Sheet2!$B$2:$Q$2))

Try this in Sheet1 G2

Please Login or Register  to view this content.

Drag/Fill Down.

Not sure where to get the second values for some cost codes, I have assumed that as your sheet is headed "PLAN" that is the criteria.

P.S.
Why Merge Sheet2 B1:Q1, it just causes problems.

[EDIT]
@ NBVC
I must have fallen asleep, I didn't see your post ...