Looking for general Approach to summing up sales volumes by selecting months

Hello everyone,

I have sales volumes for a particular year. Usually I look at 2 groupings of months due to my fiscal year changes. So for example, I'll want to look at Jan-Mar and Apr-Dec for a particular year. Lets say I have sales volume in units. I want to be able to quickly change volumes by selecting 2 months and having excel automatically calculate the volumes between and including those two months.

Let's say for some reason, I want to change it to Jan-Apr instead, so the remaining year is May-Dec. The way I envision it is to maybe have two dropdowns for months, I'd pull one down to Jan, then pull the other down to Apr, and it would lookup the values on the table for Jan, Feb, Mar, and Apr and sum those up.

Again, it may be the incorrect way, and dropdowns may be a dumb way to do it, it's just what I envision at this time. Also, I'm not looking to do the work for me, I'm just looking for your advice on a general approach. Certainly if you have built something in the past or have a previous post that is useful, I'd appreciate it.

Thank you as always.

Sum product will allow you to sum between two dates

Something like =SUMPRODUCT(--(D16:D19>=E10),--(D16:D19<=F10),E16:E19)

Where D16:D19 is the date range, E16:E19 is the range you want to sum and E10 is the start date, F10 is the end date (in your case E10 would be your start month drop down)

For the drop downs, not 100% sure but if, for example, you were using a calendar year you could create a drop down with the 1st of each month and the year.

Obviously you'd want to sum from e.g 1st Jan to 31st March so you'd need to set the End month to the right date. =EOMONTH would do that. If your End Date drop down was F10 do =EOMONTH(F10, 0) in e.g. G10 to determine the end date then just change the SUMPRODUCT formula to hit G10 instead of F10

Can't upload from work for some reason so forgive me if explanation is a bit rambling! If I can upload this form home later I will do so.

Cheers

Hi Vaslo

As you have Excel 2010 you can use the SUMIFS function to achieve this. Look at Data validation for the drop down boxes here.

Kevin

Yep, missed the Excel 2010, sumifs will be much easier for you. Apologies for overcomplication (EOMONTH is still a nice way of determining the last day of a month though )