+ Reply to Thread
Results 1 to 4 of 4

SUM column based on two dates, SUM function begins at first date which is a variable

  1. #1
    Registered User
    Join Date
    12-10-2013
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    4

    SUM column based on two dates, SUM function begins at first date which is a variable

    I appreciate your patience as I work through explaining the scenario - I do not know if I am trying to create a three dimensional array or if it can be solved.

    The spreadsheet is being used to SUM a column between two specific dates. Let's assume in the actual spreadsheet, Column A contains the list of dates beginning 1/1/2012 and continues on forever, although I am using other dates in the table below.



    Column A Current Date Column B Column C Column D Column E
    Row 1 2/27/2012
    Row 2 2/28/2012
    Row 3 3/1/2012
    Row 4 3/2/2012
    Row 5 3/3/2012

    If the current date is 3/3/2012 I need to begin the summation based on the first day of the previous month; in this example that would be 2/1/2012. So, in column B I used the DATE function

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    which should generate:

    Column A - Current Date Column B - Previous Month Column C Column D Column E
    Row 1 2/27/2012 1/1/2012
    Row 2 2/28/2012 1/1/2012
    Row 3 3/1/2012 2/1/2012
    Row 4 3/2/2012 2/1/2012
    Row 5 3/3/2012 2/1/2012

    For the next step, lets assume Column C has the Data values which need to be totaled - they begin 1/1/2012 and run through the current date.

    Column A - Current Date Column B - Previous Month Column C - units Column D Column E
    Row 1 2/27/2012 1/1/2012 1
    Row 2 2/28/2012 1/1/2012 2
    Row 3 3/1/2012 2/1/2012 0
    Row 4 3/2/2012 2/1/2012 3
    Row 5 3/3/2012 2/1/2012 2

    What I need to solve is Column D. Column D needs to total the values from Column C based on the date range generated in Column B. So, for example D1 would be totaling the cells ranging 1/1/2012 through 2/27/2012. D2 would generate the total units from 1/1/2012 through 2/28/2012. D3 needs to generate the units over the range 2/1/2012 through 3/1/2012.

    As you can see, this is presenting a challenge to me because the range selected changes based on the date. I also do not know many advanced functions and their syntax to make functions work properly.

    If the end result needs multiple formulas in multiple cells to make the end result, that is perfectly fine. This is a personal workbook.

    Attached is the workbook for the actual problem. I am trying to solve Columns AI and AJ on the 'Flight Time' workbook. AI and AJ are based on the sum of the numbers in column L and M respectively. AI and AJ need to SUM column L and M based on the date in Column A beginning from the first of the month, six months previous. (A date of 6/5/13 would sum from 1/1/13 through 6/5/13 and a date of 9/28/13 would need a sum from 4/1/13 through 9/28/13.)

    If I can add additional information to assist in the solution please post. Thanks again.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,488

    Re: SUM column based on two dates, SUM function begins at first date which is a variable

    Hi,

    Does the attached help?
    Attached Files Attached Files
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-10-2013
    Location
    US
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: SUM column based on two dates, SUM function begins at first date which is a variable

    Quote Originally Posted by Richard Buttrey View Post
    Hi,

    Does the attached help?

    Yes! Thank you very much.

    For others who are looking at similar problems, the solution was a function that looks like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    What this formula does is SUM a column of numbers based on criteria, in this case the current date and a date 6 calendar months prior to that date.

    The SUMIFS function begins with the range that is to be summed. (L:L)

    The $A:$A is the first range to evaluate. The dollar sign means that if you drag this function over multiple cells, that will remain a fixed point for the cell range.

    In evaluating $A:$A the cell will be added to the sum total IF it is "greater than or equal to" a specific date. That specific date is defined in the function as &EOMONTH($A1,-6)+1. To explain this, there is a date in cell A1. The EOMONTH function is using that date, identifying the month and then subtracting 6 months (-6). That will result in the last day of the month 7 months prior - which is why there is a "+1"... to add a day and make the result the first day of the calendar month 6 months prior to that date.

    The range L:L is then evaluated with a second set of factors. Any cell in L:L is then added to the SUM total, beginning at $A:$A if it meets the criteria of being "less than or equal to" the date specified in A1.

    Once all of the cells that do not meet that criteria are identified the remainder are totaled and presented in the cell this formula is in.

    Did I get the explanation correct?

    Thanks again for the help

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,488

    Re: SUM column based on two dates, SUM function begins at first date which is a variable

    Quote Originally Posted by moxiepilot View Post
    Yes! Thank you very much.

    ....
    Did I get the explanation correct?

    Thanks again for the help
    Yes indeed, that's about right. Looking at it again I realise it could also be

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    i.e. avoid adding one day to the date 6 months prior and just use a > test rather than a >=

    Thanks for the rep point.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Sum column based on variable start and variable end months
    By Steve N. in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-06-2013, 08:01 PM
  2. Replies: 0
    Last Post: 05-30-2013, 07:50 PM
  3. [SOLVED] Value updating based on variable dates
    By D-smoke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2013, 09:37 AM
  4. Populating column with dates based on start and end date
    By mdavid800 in forum Excel General
    Replies: 5
    Last Post: 08-07-2011, 12:49 PM
  5. Sum cells based on a row variable and seperate column variable
    By CheeseHeadTransplant in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-23-2005, 02:05 PM

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