• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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.

PLEASE
Sumif-Dates.xlsx
0
user2073
Asked:
user2073
  • 2
  • 2
1 Solution
 
barry houdiniCommented:
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
 
user2073Author 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
 
barry houdiniCommented:
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
 
user2073Author Commented:
The new lines of code both work well.
I appreciate your help.

Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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