Sumif/Sumproduct with a column match?

[tightwad]

I have 4 sheets of data I am combining into one report. Down the left side there are columns for Region, District, and Market, and across the top are week numbers.

I want to sum the data where Region = value in B1, and week# = the value in C1

If I knew the column I needed all the time it would be easy to use a Sumif, or multiple matches use Sumproduct...but the column needed changes depending on the week #

 

[barry houdini]

You can get the range for the SUMIF with INDEX, e.g. with week numbers in B1:J1 and Regions in A2:A10

=SUMIF(Data!A2:A10,B1,INDEX(Data!B2:J10,0,MATCH(C1,Data!B1:J1,0)))

 

[tightwad]

Worked like a charm! I knew there would be a way, I just couldnt envision it.