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.

Find more posts tagged with

Accepted answers

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

All comments

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. 👍️

Sign in to join the conversation!

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.