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

How can I use SUMPRODUCT to sum the last 12 months

I have used the SUMPRODUCT formula to return the addition of the last 12 months of data
from another worksheet using Item, Month and Year as criteria. Can anyone correct the formula in the attached file.

I'm trying not to use any extra columns and will eventually use the formula in a macro.

THANKS
Sumif-Dates.xlsx
0
user2073
Asked:
user2073
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Two things are obviously wrong

sumproduct uses direct formulas unlike sumif where the criteria is a string like ">"&...
and the other is that sheet1 should be inside the EDATE function and not outside it.

Here is the corrected formula. You can test the values returned

=SUMPRODUCT((Sheet1!$B$7:$B$54=Sheet2!A8)*(Sheet1!$D$7:$D$54>EDATE(Sheet2!$B$3,-12))*(Sheet1!$C$7:$C$54=YEAR(Sheet2!$B$3))*(Sheet1!$G$7:$G$54))
0
 
user2073Author Commented:
Great this formula works well, I'm inspired by the knowledge you have.


THANKS
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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