Sumproduct with Dynamic Column Headings

Hello,

I've got a sumproduct formula based on multiple criteria, that is currently based on static columns (4Q16 data). I want the ability to have a toggle field (cell D31) that will allow me to switch the columns being used in the sumproduct based on the setting in the toggle field (eg. change to 3Q16 Data). I have attached my workbook to help find a solution. The highlighted portions of the formula (blue and green) are what I need to be tied to the column heading (cells E33 & E34) determined by the toggle cell (D31). I'm thinking INDEX MATCH, but I can't figure out how to incorporate that into my sumproduct formula.

Please let me know if you need me to clarify anything and I thank you in advance for your assistance.

Hi Ali,

Thank you for your proposed solution, however, this is just a small piece of the data I am working with (I reduced the file size to satisfy the excelforums file upload parameters). I actually have a lot of historical data (goes back several years) and I would rather not have to build all those choices (eg. 1Q14, 3Q12, 2Q10) into the formula. I've been able to build a sumifs formula that uses the index match logic that I'm thinking of (see below), however, I can't seem to replicate for my sumproduct formula.

=SUMIFS(INDEX($C$16:$H$2000,0,MATCH((TEXT($D$29&" Curr P18-49 Imp",0)),$C$15:$H$15,0)),$C$16:$C$2000,"WML",$H$16:$H$2000,"Y")

Very quick reply from your sample file but something along the lines of
=SUMPRODUCT((C16:C23="WML")*(H16:H23="Y")*(OFFSET(C16,0,D31,8,1))*(OFFSET(D16,0,D31,8,1)))/SUMPRODUCT((C16:C23="WML")*(H16:H23="Y")*(OFFSET(D16,0,D31,8,1)))

where d31 is =MATCH(E31,D15:G15,0)

might work, as the ranges will dynamically change as a result of the match function, hopefully a start

Hi John,

Unfortunately, the report I will be dumping in will have a dynamic number of rows, so based on the formula you offered me, i would have to count the number of rows in the report each time and change the row count in the offset formula. I was envisioning, the only cell that I change (after dumping in the new data) would be the toggle switch (no changes to the formula). That's why I keep coming back to Index Match, because that should search for you no matter how many rows of data there are in the report each time. (that's also, why the formula was set to 2000 rows in the first place, because i know it will never be MORE than 2000 rows of data). I apologize if I should have stated this in my initial post (I'm quickly learning forum etiquette)

I update the K30 to D29 to match the spreadsheet, but unfortunately, that did not work. I am getting #VALUE. I feel like we're so close now that we have the Index Match embedded.

=IFERROR(SUMPRODUCT(($C$16:$C$2000="WML")*($H$16:$H$2000="Y")*(INDEX($D$16:$G$2000,,MATCH($D$29& "*",$D$15:$G$15,0)))*(INDEX($D$16:$G$2000,,MATCH($D$29& "*",$D$15:$G$15,0)+1)))/SUMPRODUCT(($C$16:$C$2000="WML")*($H$16:$H$2000="Y")*(INDEX($D$16:$G$2000,,MATCH($D$29& "*",$D$15:$G$15,0)+1))),"")

K30 is your drop down (Period) selection

Thank you John. This worked perfectly. Reputation stars are on on the way!!

Thank you the feedback and rep.

Glad it's sorted!