SUMIFS with multiple criteria and OR function with multiple cross sheet references?

Tim Hanson

The Situation: (for context, otherwise, skip to "The Problem")

Our PMs manage their projects on individual schedules, they assign the "scope" of a task, and they adjust the start and end dates for the various tasks, as well as the hours estimated to complete the tasks, and the amount of people assigned to complete the tasks.

So any given row in their schedule will provide: Start Date, End Date, Scope ("Design Assist", Detailing, etc.) and the hours spent per week on that task.

Every week an automation carries all of the rows to a "Bandwidth Collection Sheet" that identifies the newest version of that task (using a unique row ID that is also imported and the created dated), and automatically removes the old information.

The end goal is to build a chart on a dashboard that forecasts the labor allocated to the various scopes of work across all current and upcoming projects.

To do this I have made a "Summary Sheet." It has "Week Start" column, which has the date of every Sunday for the next 4 years (this is a function so it always starts on the current week and projects out four years.) It has a "Week End" column, which is every Saturday.

The Problem:

The Summary Sheet has four columns, one for each scope of work that we want to forecast the weekly labor projections. The columns are: "Design Assist Hours", "Detailing Hours", "Project Management Hours", and "Field Hours".

I need to create a function that works for each of these rows that provides the sum of the hours assigned to that {Scope}, if the {Start Date} of the task is between the "Week Start" and "Week End", OR if the {End Date} is between the "Week Start" and "Week End", OR if any of the duration the task occurs between the "Week Start" and "Week End".

This function is getting real messy, real fast. Any suggestions?

Here is the summary sheet where I'm trying to get the sum of the hours assigned to a scope in any given week.

The function populating these columns is currently only summing if the by scope and if the start and stop date land within a single week, which is ultimately inaccurate...

=SUMIFS({Bandwidth Collection Sheet Hours Per Week}, {Bandwidth Collection Sheet Company}, "CPI - Field", {Bandwidth Collection Sheet Start Date}, >=([Week Start]@row), {Bandwidth Collection Sheet Due Date}, <=([Week End]@row))

Tim Hanson

Oh my goodness, that worked. I was overthinking this way too much.

Here is the final formula, working perfectly.

=SUMIFS({Bandwidth Collection Sheet Hours Per Week}, {Bandwidth Collection Sheet Company}, "CPI - Design Assist", {Bandwidth Collection Sheet Start Date}, <=([Week End]@row), {Bandwidth Collection Sheet Due Date}, >=([Week Start]@row))

Paul Newcome

To cover any overlap, switch the start and end date criteria.

Basically you want anything where the {Start} is less than or equal to [Week End] and the {End} is greater than or equal to the [Week Start].

Tim Hanson

Oh my goodness, that worked. I was overthinking this way too much.

Here is the final formula, working perfectly.

=SUMIFS({Bandwidth Collection Sheet Hours Per Week}, {Bandwidth Collection Sheet Company}, "CPI - Design Assist", {Bandwidth Collection Sheet Start Date}, <=([Week End]@row), {Bandwidth Collection Sheet Due Date}, >=([Week Start]@row))

Paul Newcome

Happy to help. 👍️