Hello,
I am trying to get the sum of a range based on a dropdown menu option, that chooses from a data sheet. So I have a data sheet that has all the days of the year listed, and depending on the week, another column that says "MW1", "MW2", etc. so days 1-7 are all listed as MW1 and 7-14 are MW2 and so on.
What I am trying to do is first i have a sumif formula that pulls from the dropdown list (which is all the MW1,MW2,MW3,etc), so if i select MW2 it sums all the numbers from the data sheet column I selected that have the dates and week listed of MW2. What i want to do now is compare this week I selected with the week prior, so my thought was to do a Sumif formula and then offset the range up 7 days prior. Nothing has worked and I'm not sure i am structuring this properly. I first tried this:
=SUMIF('Daily KPIs New'!A:A,K2,OFFSET('Daily KPIs New'!D:D,-7,0,7)) where K2 is the drop down list, and it sums the ranges from the data sheet.
Then after some research I saw sumif + offset formulas look like this, =SUMIF(OFFSET('Daily KPIs New'!D:D,-7,0,7),K2,'Daily KPIs New'!D:D) but i don't think the offset knows where to look or start based on this. I am new to advanced excel and forums so any tips would be helpful! If this is not the best way to go about it, let me know!
Bookmarks