SUMIF w/ sum range being dynamic ROW title

[JL_User]

I have a table of data that's associated with a channel, metric (rows) and months (column).

I'm trying to create a view like below where it summarizes the data depending on what time frame the user wants to see. B3, C6 and C9 are hard-coded inputs. Based on those inputs, I'm trying to figure out what the formula is for C14:E16. I think it's some combination of SUMIF and INDEX MATCH, but I haven't been able to figure out how to best structure the formula.

Thanks for the help in advance.

 

[Z51]

This formula in C14 on the summary view, filled down and right, should hopefully work...

=IF($B$3="Monthly",INDEX(Sheet1!$E$3:$P$13,MATCH($C14&D$13,Sheet1!$C$3:$C$20&Sheet1!$D$3:$D$20,0),MATCH($C$6,Sheet1!$E$2:$P$2)),IF($B$3="Quarterly",INDEX(Sheet1!$R$3:$U$13,MATCH($C14&D$13,Sheet1!$C$3:$C$20&Sheet1!$D$3:$D$20,0),MATCH($C$9,Sheet1!$R$2:$U$2))))

Adjust your sheet name and ranges of your full data as needed. This looks at Sheet1 and rows 3:20.

 

[Eric W]

Welcome to the MrExcel forum!

I haven't been able to test this, since I didn't want to retype everything. (See the XL2BB tool for a better way to present your data.) But this should work for you based on what you showed:

C14:

=SUMPRODUCT(Sheet1!$E$3:$U$13*(Sheet1!$C$3:$C$13=$B14)*(Sheet1!$D$3:$D$13=C$13)*(Sheet1!$E$2:$U$2=IF($B$3="MONTHLY",$C$6,$C$9)))

Drag down and across as needed.

 

[JL_User]

Amazing - thank you so much!