How can I sum a column using dates for the last 12 months

Posted on 2012-09-03
Last Modified: 2012-09-03
I'm using a sumproduct formula in the attached file "Sheet2", column "K" which adds up "Sheet1", column "G" which meet the three criteria.
The problem is the criteria looks for the full year for the previous year not only the months to makeup the 12 months as required. The "EDate" formula is fine, except I need it to include the full 12 months of data from the the value in "Sheet2", cell "B3", eg. (Sep 2011, Aug 2011, Jul 2011, Jun 2011, May 2011, Apr 2011, Mar 2011, Feb 2011, Jan 2011, Dec 2010, Nov 2010 and Oct 2010) - ONLY.

Does anyone have a solution.

Question by:user2073
    LVL 50

    Expert Comment

    by:barry houdini
    Try this formula for K9 copied down


    That will sum all the relevant values from 12 months before B3 and later (so if B3 is October 2012 that will sum for Dates on or later than October 2011). It takes the month and year in columns C and D and concatenates those with 1 to make a date which can be compared with the date returned by EDATE

    Does that work for you? If not please indicate some required results for specific rows/dates

    regards, barry

    Author Comment

    The formula works for October 2012, as you have described. If I change "Sheet2", Cell "B3" to say August 2012 the results return September 2012 & October 2012. Is there a method to restrict the calculate all values between and including  July 2012 & August 2011.

    Thank you for your quick response.
    LVL 50

    Accepted Solution

    For that you can add another criteria to ensure that dates are before B3, i.e.


    so that will sum for the 12 months before B3 (not including B3)

    Another way is to explicitly list the months like this


    regards, barry

    Author Closing Comment

    The new lines of code both work well.
    I appreciate your help.


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now