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

# 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
1 Solution

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

Author Commented:
Great this formula works well, I'm inspired by the knowledge you have.

THANKS
0

## Featured Post

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