• Status: Solved
• Priority: Medium
• Security: Public
• Views: 376

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

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
user2073
• 2
• 2
1 Solution

Commented:
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 Commented:
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

Commented:
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 Commented:
The new lines of code both work well.
I appreciate your help.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.