Formula to sum all cells in 1 column above a certain cell with criteria.

Hello,

I'm looking for a way to sum a range of cells where the range is dependent on what I choose from a dropdown menu. So I have a dropdown menu of 52 options (one for each week of the year) and if i choose for example, Week 12, the formula would sum everything from week 1 to week 12. Then have another formula that if I select week 12, it would sum everything up to week 11.

In attached excel, on the "Weekly Worksheet" tab, in F2 i have a dropdown. if i select one of the options, i would like if in B2 it would sum the range up to that week, and in C2 it would sum the range before that. For example if I choose MW12, then B2 sums the range in the "Daily KPIs New" tab, column D, from D2:D85. Then C2 would sum the range D2:D78.

I am already using this formula to sum the total for each week, =SUMIF('Daily KPIs New'!A:A,'Weekly WorkSheet New'!F2,'Daily KPIs New'!D:D) , but this gives me the weekly sum, so now I need the full year up to that week basically.

Hope this makes sense.

Hi ScardiB,

Not sure if this is the most elegant solution but I've tested it and it works.

For your 'YTD Actual' in cell B2, use the following:
=SUM(INDIRECT("'Daily KPIs New'!D2"&":D"&MATCH(F2,'Daily KPIs New'!$A$1:$A$365,0)+6))

For your 'YTD Goal' in cell C2, use the following:
=SUM(INDIRECT("'Daily KPIs New'!D2"&":D"&MATCH(F2,'Daily KPIs New'!$A$1:$A$365,0)-1))

The Match formula looks for the corresponding row number matching your week selection, going either to the end of the week (+6) or to the end of the previous week (-1).
The Indirect formula then turns that number into a cell reference that can be used by Sum.

Regards,

Daniel.

This worked! Thank you, I was able to use the formulas to pull from other range areas on other tabs as well and it translated through.

thank you

This worked as well! When I tried to use the same structure of the formula on another data range and sheet, it didn't pull the right numbers for some reason, even though I set it up just like this with the correct columns and references.

But either way, it worked for my initial question. Thank you!