How to sum values in a cell based on information in a drop-down menu.

[Aaiken353]

Hi All,

I am just wondering if any Excel savvy people could tell me what formula I should use in order to sum the values of a cell in a different sheet based on information from a drop down menu. Basically I would like to be able, for my job have a Month to date figure based on the accumulated values from each week, and have them change based on the I information in my drop down menu.

[#2]

@Aaiken353 

That's bit abstract. Do you have hundreds of sheets and their number is variable, or few fixed number of sheets. What is the logic which links returned from drop-down value and values to sum - by position, by ID, by some calculations, etc. Is it one number from each sheet to sum or ranges which are to be summed. Perhaps more questions, but better to have small sample file which illustrates the task.

[#3]

Hi Sergei,
Thanks for getting back to me. I've attached a file. Basically I want to know, using the file as an example, in sheet2 I have weeks with a TY collum and a LY collum, TY figures are entered weekly, but LY figures are all inputted, my issue arises when we move to sheet1 which has months, and a TY and LY collum and a cell 2 has my drop down list with weeks in it. Basically, I now want my LY collum to only sum up the figures based on which week I'm in. So in march when I select week 5, LY collum should be the sum of week 1 to week 4 and April LY should be the sum of only week 5. And then when I select week 6 from my drop down list, March LY remains the same but April LY is now the sum of week 5 and 6 from sheet 2 LY collum. I hope this makes sense.

[#4]

@Aaiken353 

Thank you for the file. Main question here is how Excel knows which week belongs to which number. Assume your Week #1 is from March - from first of march even if it is Sunday, or from first Monday of March or something else. Next, let assume 3 days of Week #5 are in March, rest are in April. Is that March week or April week?

We may create helper table with such mapping, but it will be for the concrete year. Next year days could be shifted and Week #5 from March will go on April. Better to define formal logic, after that with help of WEEKNUMBER() calculate which week is in which month.

As soon as we know that we may calculate the summary for each month, depends on selected week is within the month or after / before it.

[#5]

Hi Sergei,

Thanks for the response, I think I'm a little confused. Basically I know which weeks belong to which months. In my example week 1 to week 4 is March and week 5 to week 8 is April. They are set, days don't change them and they are not set on a calender month. They are a fiscal representation of a period.
I am just wondering it it is possible to get sheet 1 LY collum to sum the figures from Sheet 2 LY collum based on the option from a drop down menu. For example if we take it that this current week we are in is week 6. Which is the second week of April then I just want the LY collum in sheet 1 next to April only sum the figures for week 5 and 6 into the collum and in the LY column it for March it would have summed all of the figures from sheet 2 LY column week 1 to week 4 . And this would be based on the change in my drop down menu which would now have selected week 6 as that is the week that I am in. Then continuing with this example. Next week i will be in week 7, therefore I will select week 7 on my drop down list on sheet 1. And then in the April LY column the figures will sum all the figures from sheet 2 LY collum week 5 to week 7 and March will remain the unchanged. I hope that's a better explanation. I'm not sure if what I'm wanting to do is possible, I thought it would have something to do with a whatif or lookup formula.

[#6]

@Aaiken353 

When it'll be more complex.

First, we need to transfer to Excel your knowledge that WEEK1, WEEK2, WEEK3, WEEK4 are belong to March, another ones to April, etc.

Second, we need normalize names of weeks. Somewhere they are like "Week1", somewhere "Week 1" which is not the same. Sorting and compare texts could give wrong results. For example, if we compare texts like Week2 and Week12, first will be greater (or later) than the second. 

Without formal logic of how to calculate weeks we may add helper range at any place of the workbook as

note, we use numbers for weeks, not texts. With that formula could be

=SUMPRODUCT(
   ($E$4:$E$11<=--SUBSTITUTE($A$2,"WEEK",""))*
   ($F$4:$F$11=$A4)*
   (--SUBSTITUTE(Sheet2!$A$3:$A$10,"WEEK ","")<= --SUBSTITUTE($A$2,"WEEK",""))*
   Sheet2!$C$3:$C$10
)