Sum of Values Between Two Dates

Lauren Mullane

Hi All!

I am trying to write a formula that add up the total "crew size" required between two dates. I've created a metric sheet to hold the formula. See screenshot of parent sheet below.

Formula:

=SUMIFS({Shop Overall Schedule Range 1}, {Shop Overall Schedule Range 2}, >=DATE(2023, 9, 1), {Shop Overall Schedule Range 3}, <=DATE(2023, 9, 15))

Shop Overall Range 1: Crew Size

Shop Overall Range 2: Start

Shop Overall Range 3: Finish

The formula seems to be dropping out totals that are not associated with a start or finish date. For example, week of September 17th, it should show a total of (3), but it is showing (0). I've tried AVGCOLLECT and COUNTIFS, but haven't gotten anything to work.

Paul Newcome

Which columns are each of your ranges referencing?

Jon Mark H

@Lauren Mullane

It looks like the issue is with your date range, as in your shown formula, the SUMIFS will check that BOTH the start date is 9/1/23 or after AND that the finish date is less than or equal to 9/15/23.

Your screenshot doesn't show any project that matches this criteria.

Can you explain a little more about your context - for instance - is your time frame you're wanting to look at always (2) weeks or does it vary?; Are you trying to project capacity in terms of how many workers are needed/available?

Hope I can help with a bit more information.

-Jon