Solved

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

Posted on 2012-09-03
370 Views
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.

Sumif-Dates.xlsx
0
Question by:user2073

LVL 50

Expert Comment

Try this formula for K9 copied down

=SUMPRODUCT((Sheet1!\$B\$7:\$B\$54=A9)*((1&Sheet1!\$D\$7:\$D\$54&Sheet1!\$C\$7:\$C\$54)+0>=EDATE(\$B\$3,-12)),Sheet1!\$G\$7:\$G\$54)

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
0

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.
0

LVL 50

Accepted Solution

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

=SUMPRODUCT((Sheet1!\$B\$7:\$B\$54=A9)*((1&Sheet1!\$D\$7:\$D\$54&Sheet1!\$C\$7:\$C\$54)+0>=EDATE(\$B\$3,-12))*((1&Sheet1!\$D\$7:\$D\$54&Sheet1!\$C\$7:\$C\$54)+0<\$B\$3),Sheet1!\$G\$7:\$G\$54)

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

Another way is to explicitly list the months like this

=SUMPRODUCT((Sheet1!\$B\$7:\$B\$54=A9)*((1&Sheet1!\$D\$7:\$D\$54&Sheet1!\$C\$7:\$C\$54)+0=EDATE(\$B\$3,-{1,2,3,4,5,6,7,8,9,10,11,12}))*Sheet1!\$G\$7:\$G\$54)

regards, barry
0

Author Closing Comment

The new lines of code both work well.

Thanks
0

## Featured Post

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 …