Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Sum Values Between Two Dates

KCS ✭✭
edited 12/09/19 in Archived 2017 Posts

I'm looking for some help with a formula.  I've created a complicated system of sheets where a contractor enters her/his hours into a form each day they work and I link the data into a contractor's individual sheets which then feed into a series of formulas which feed into reports which feed into sights.

What I need to do is to create a formula within each individual sheet that calculates the hour's that that person has worked each week.

So I created a Week Start Date column, a Week End Date column, and a Total Hours worked per week column.  This is the formula I created that keeps coming up #unparseable:

=SUMIFS([NG Hours Worked]:[NG Hours Worked],[NG Date]:[NG Date],[NG Date]>=[AJ Week Start]3,[NG Date]:[NG Date],[NG Date]<=[AJ Week End]3)

NG Hours Worked is a column that has a formula that brings in only the hours that this person worked.  This ultimately comes from the form that this person entered her/his hours.

NG Date is a column is a column that has a formula that brings in the date of the hours that this person worked.  Same source as above.

Everything I look up tells me that my formula is correct, however I cannot get it to work.  Any help would be greatly appreciated!




  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    You were almost there. Try this in the first cell and drag down:

    =SUMIFS([NG Hours Worked]:[NG Hours Worked], [NG Date]:[NG Date], >=[AJ Week Start]1, [NG Date]:[NG Date], <=[AJ Week End]1)

    You don't need to refer to the criterion_range again (e.g. [NG Date]) in the criterion (e.g. <=[AJ Week End]1), as you've already established it in the criterion_range.

    Hope this makes sense.

  • KCS
    KCS ✭✭

    Thank you Chris!!!  This worked perfectly!!  I appreciate the help and the quick response!!

    Not only does it work as I need it to, but I learned something new for future formulas!!

  • Chris McKay
    Chris McKay ✭✭✭✭✭✭

    My pleasure. Glad I could help :-)

  • I am trying to get a Formula that I can use to pull the total Mileage my contractors have covered within the week. I have a completion date column and a total mileage column I am just not sure how to use the SUMIF Formula to get the mileage within only the current week? 

    this is what I have but I am getting  #UNPARSEABLE , I am sure that I have more than one thing wrong.

    =SUMIF([Fielding Complete Date]:[Fielding Complete Date], ">="10/15/18,"<="10/23/18, [Total Mileage]:[Total Mileage])

    Can anyone help?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭


    Try this.

    =SUMIFS([Total Mileage]:[Total Mileage]; [Fielding Complete Date]:[Fielding Complete Date]; WEEKNUMBER(@cell) = WEEKNUMBER(TODAY()))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    I hope this helps you!


    Andrée Starå

    Workflow Consultant @ Get Done Consulting


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    Hi all,

    I've seen a couple of threads similar to this, but am running into some issues.

    I have 2 sheets: One is a gantt sheet with Start, End, and Crew Size columns. The other is a one with a range of days, and a column for total crew size for that corresponding day.

    I'm trying sum the crew size all tasks on the gantt back to the Summary sheet for that day. I've attempted using the sumif() and sum(collect()) formulas, but am not getting the expected results. Does anyone have any ideas/insight?

    =SUMIFS({Daily Crew}, {Start}, >=Date@row, {End}, <=Date@row)

    =SUM(COLLECT({Daily Crew}, {Start}, >=Date@row, {End}, <=Date@row))


    Daily Summary.JPG

  • Chris Mondeau
    Chris Mondeau ✭✭✭✭✭✭

    Got some help from the Smartsheet team (thanks Lance Merkin!)

    The solution was to use the @cell function in conjunction with the sumifs logic statement:

    SUMIFS to be =SUMIFS({Daily Crew}, {Start}, Date@row >= @cell, {End}, Date@row <= @ cell)

    Works like a charm!

This discussion has been closed.