Formulas between sheets

SStern

Hi there,

I am trying to add up some numbers found on one sheet in a second sheet, but only when certain criteria are met. I believe this should be a SUMIF formula, but I'm not sure how to make both the reference to a different sheet and the criteria work together. The closest I can wrap my head around is =SUMIF({Contractor Invoice and Hour Tracker Range 2}, "Jimmy")

In my case, I am looking to add hours worked but only for specific people using either the name or email column. In the images below, I need the "Staff Contractor Information" sheet to show that Susan worked 70 hours and Jimmy worked 30 hours, as noted on the "Contractor Invoice and Hours Tracker" sheet. I also need that cell to stay updated each time Susan or Jimmy add a new row.

Additionally, I would like to add a column in "Staff Contractor Information" that shows hours remaining - so subtracting the running "Total Hours Worked" total from a manually-inputted limit. Is there a formula for subtraction?

Thank you in advance for your help.

Sandra Guzman

Hello @SStern,

You are on the right path and very close to solving the formula puzzle. If you run various queries in this community you may be able to find some help as it relates to solving for some of these walls.

Try this in your Staff Contractor Info sheet:

=SUMIF({Submitter Name}, Contractor Name@row, {Numbers of Hours Worked for This Invoice})

The formula will be looking at the 'Submitter Name' column and matching it against the name in Staff Contractor Info sheet under the 'Contractor Name@row, then adding numbers in the column for hours worked.

You can find more information on cross sheet formulas here:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Smartsheet can also do subtraction but it depends on how you plan to set this up. If you plan to enter a value for the limit you could use:

=100 - [Total Hours Worked]@row

100 being the limit manually entered minus the total hours worked. Hope this helps.

All the best,

🌻 Sandra

SStern

Thanks Sandra, but I'm getting an error. I'm linking the {Submitter Name} and {Numbers of Hours Worked for This Invoice} to the columns in Contractor Invoice and Hour Tracker, not sure what the issue may be.

SStern

I was actually just able to make this work by using "Susan G.", and "Jimmy", making sure there were quotation marks around the name. Thanks for your help!