Help: Cross Sheet SUMIFS by month between two dates

Robert Meisch

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.

Paul Newcome

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))

Robert Meisch

I tried this:

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

Calendar Data Range 3 = Start date column

Calendar Data Range 4 = End date column

But am still getting 5 for Syracuse in December 2021.

Paul Newcome

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))

Robert Meisch

That seems to have done the trick.

Thank you Paul!

Paul Newcome

Happy to help. 👍️