+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2013
    Posts
    200

    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.

  2. #2
    Registered User
    Join Date
    12-11-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 365
    Posts
    91

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

    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

  3. #3
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

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

    Something like this could helps you?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

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

    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

  5. #5
    Registered User
    Join Date
    12-11-2012
    Location
    Manchester, UK
    MS-Off Ver
    Excel 365
    Posts
    91

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

    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 )

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1