Help with SUMIF formula

Hello Community,

I need to create a report that shows capacity by month.

My sheet tracks resource allocation on projects with resource start and end dates. (We can't afford the Resource Management module so we're doing it in Core SS).

I've been asked to do a "heat map" of sorts where each column is a month. For each row (Resource) we want to sum their allocation.

This is my allocation tracking sheet. Each resource has one (or more) rows per project on the sheet.

Here is where I want to see their allocation by month. So I'm thinking I need a "sum allocation if start date or end date are in March" for example. But I can't seem to get the formula to work. I can't use AI because I'm crossing sheets.

Once I get this working, I just have to apply some conditional formatting and I'm done!

Any assistance would be greatly appreciated.

Find more posts tagged with

Cross Sheet Reference

fromula

Accepted answers

Paul Newcome

I would suggest using a bit of a different approach. If my start date is 12/31/25 and my end date is 1/1/27, I won't get counted for March of 2026. The start month is greater than 3, the end month is less than 3, and neither of the years match 2026.

My recommendation would be to use the DATE function instead and compare the start date to less than the first of the next month and the end date greater than or equal to the first day of the month.

May 2026 would look like this:

=SUMIFS({Allocation}, {Resource}, @cell = Resource@row, {Start Date}, @cell < DATE(2026, 06, 01), {End Date}, @cell >= DATE(2026, 05, 01))

June 2026:

=SUMIFS({Allocation}, {Resource}, @cell = Resource@row, {Start Date}, @cell < DATE(2026, 07, 01), {End Date}, @cell >= DATE(2026, 06, 01))

All comments

DKazatsky2

Hi @Andrea Zenner,

The following formula should get you what you are looking for. NOTE: This formula needs to change for each month/year column.

For March 2026: =SUMIFS({Allocation}, {Resource}, Resource@row , {Resource Start Date}, AND(MONTH(@cell ) <= 3, YEAR(@cell ) = 2026), {Resource End Date}, AND(MONTH(@cell ) >= 3, YEAR(@cell ) = 2026))

For April 2026: =SUMIFS({Allocation}, {Resource}, Resource@row , {Resource Start Date}, AND(MONTH(@cell ) <= 4, YEAR(@cell ) = 2026), {Resource End Date}, AND(MONTH(@cell ) >= 4, YEAR(@cell ) = 2026))

Notice the number after MONTH(@cell) needs to correspond to the month you are referring to per column.

Hope this helps,

Dave

Paul Newcome

My recommendation would be to use the DATE function instead and compare the start date to less than the first of the next month and the end date greater than or equal to the first day of the month.

May 2026 would look like this:

=SUMIFS({Allocation}, {Resource}, @cell = Resource@row, {Start Date}, @cell < DATE(2026, 06, 01), {End Date}, @cell >= DATE(2026, 05, 01))

June 2026:

=SUMIFS({Allocation}, {Resource}, @cell = Resource@row, {Start Date}, @cell < DATE(2026, 07, 01), {End Date}, @cell >= DATE(2026, 06, 01))

Andrea Zenner

@Paul Newcome You are a AWESOME! That is perfect, thank you so much!

Paul Newcome

Happy to help.

Sign in to join the conversation!

Quick Links

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.