SUM multiple rows based on corresponding drop down lists

[stewartc3]

Hi,

I have 2 date drop downs in I2 and J2. They are lists that have the entire years dates in them. I am looking to sum values based on the dates listed in B. For example, If I choose date 1 to be 12/28 and date 2 to be 1/3, I would like it do to the same as =SUM(C2:C8)/SUM(F2:F8). I want to just be able to select the date ranges and have excel calculate it. Is this even possible? I've attached a screen shot.

Thank you.

 

[dnorm]

Hi Stwerartc3

The simplest way I can think of is to convert your data in to a table, then use a slicer for the date filter (Main Ribbon, Insert, Slicer and select day). On the slicer you can pick the start date then find the end date and hold shift when selecting it. that will highlight all dates in between. The formula to calculate your outcome will come from the total row (a check box option in the table tools ribbon).

hope this helps

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

=sumifs(C:C,B:B,">="&I2,B:B,"<="&J2)/sumifs(F:FC,B:B,">="&I2,B:B,"<="&J2)

 

[stewartc3]

Thanks, I'm getting a #VALUE! with =SUMIFS(C:C,B:B,">="&I2,B:B,"<="&J2)/SUMIFS(F:FC,B:B,">="&I2,B:B,"<="&J2)

 

[Fluff]

Oops typo in the 2nd sumifs, it should be F:F not F:FC

 

[stewartc3]

Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dnorm]

Thanks @Fluff, I did think about SUMIFS, but could not get the date variations to work.

You're welcome & thanks for the feedback.

Cheers for the approach, I did think about SUMIFS, but was stuck dealing with dates; hence why I went with a slicer and table..