Help: Cross Sheet SUMIFS by month between two dates

So I have some data showing the impact level of projects at sites with Start and end dates for each row:

And I want to build a metrics sheet summing the impact by month for each site:

I started with the following:

=SUMIFS({Calendar Data Range 1}, {Calendar Data Range 2}, =Site@row, {Calendar Data Range 3}, AND(IFERROR(MONTH(@cell), 0) >= 12, IFERROR(YEAR(@cell), 0) = 2021), {Calendar Data Range 4}, AND(IFERROR(MONTH(@cell), 0) <= 12, IFERROR(YEAR(@cell), 0) = 2021))

Which doesn't work because both criteria must be met. This tallies a 5 for Syracuse because only the 3rd row meets both criteria. However, in this case Syracuse should have a total impact of 8 for Dec 2021 as both row 2 and 3 show projects running during December 2021.

Any ideas on how to calculate this would be appreciated. Thank you.

Robert Meisch

Sysco Corp.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this...

    =SUMIFS({Calendar Data Range 1}, {Calendar Data Range 2}, =Site@row, {Calendar Data Range 4}, @cell >= DATE(2021, 12, 1), {Calendar Data Range 3}, @cell <= DATE(2021, 12, 31))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!