Sum Values Between Two Dates

KCS

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!

Thanks

~KC

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

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

My pleasure. Glad I could help :-)

e.mcinnes66356

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å

Hi,

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!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

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))

Thanks!

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!