Formula to find the correct column in a range, and sum the values in that column according to multiple criteria

[Will85]

I have a report that I receive weekly

Column A will always be Region
Column B will always be Color
Columns C through Z may change, the order may change, a column that had activity the prior week might not exist because their is no activity.

The headers in Column C through Z are known to me. For example, "Unit Sales".

"Unit Sales" might have been column G one week, and then it became column Y in the next.

I need a formula that I don't have to keep changing each week, that can look for "Unit Sales" in columns C through Z, find the correct column, and then sum the values in each row in that column that have a specific Region and Color.

For example, find Unit Sales, and sumif Region is North and Color is Orange.

 

[mikerickson]

Try
=SUMPRODUCT((C1:Z1="Unit Sales")*C2:Z1000*(A2:A1000="someRegion")*(B2:B1000="some Color"))

 

[Eric W]

Or:

=SUMIFS(INDEX(C:Z,0,MATCH("Unit Sales",C1:Z1,0)),A:A,"someRegion",B:B,"some Color")