Sum of Values Between Two Dates

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.


=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
    Paul Newcome ✭✭✭✭✭✭

    Which columns are each of your ranges referencing?

  • Jon Mark H
    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!