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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!