Nested SUMIF formulas across multiple sheets

I am trying to create a couple of formulas that don't seem to be working properly.

For the first sheet, I am creating a Document that lists Job titles and the hourly rate. Each job title can have multiple hourly rates. We will call this doc "Hourly Jobs Breakdown". (Rates will all be in $00.00 format, I just forgot to format Column4 before taking a screenshot)

"Hourly Jobs Breakdown":

I have then created a "Labor Invoice Tracking" document, where "Labor" is a drop down menu consisting of the labor titles in "Hourly Jobs Breakdown", as well as some other jobs that are not paid by the hour.

"Labor Invoice Tracking" Document :

My Goal here, is a couple of things, that I think use the same formula structure.

#1: To create a formula for "Rate", that references back to the "Hourly Jobs Breakdown" sheet, and allows me to choose which hourly rate I would like for each job. (Choosing Fabrication at $45, $65, or $85).


Currently, I have the formula above, but I have to pick a rate I want it to link to, and manually change this formula if I'm changing the rate.

Would a "lookup" be more useful here? I couldn't find a lookup formula that allowed for multiple criteria . I would like it to "lookup" the labor title I select, and in the "rate" column, give me the option of "$45, $65, or $85".

IF Labor Column on this sheet = "Fabrication" -> Go to "Hourly Jobs Breakdown" sheet -> Give me the values for "Hourly, Rate Change, Rate Change" as an option.

"Hourly Jobs Breakdown" Sheet:


#2: At the top of "Labor Invoice Tracking, I would like to sum up the subtotals of all jobs at their hourly rate.

I have figure out the formula to add all subtotals under a certain job:

=SUMIF(Labor:Labor, @cell = "Fabrication", SUBTOTALS:SUBTOTALS)

#Obviously @cell = changes depending on the labor title.

Now I want to give it another Criteria, and say IF the labor is "Fabrication" and IF the rate is "$45", add up the subtotals. I wrote:

=SUMIF(Labor:Labor, (@cell = "Fabrication", IF(Rate:rate, @cell = $45.00)), SUBTOTALS:SUBTOTALS)

However, it gives me "UNPARSEABLE".


#3: The final part, is to report those numbers back to an "Overall Project Budget".

The issue here is with the "Quantity". (I have changed Column8 to Quantity)

For example, with "Fabrication" (in the cell that says "#INCORRECT") :

I want to go back to "Labor Invoice Tracking" sheet. Under the Labor column, IF the labor is "Fabrication" IF the rate is "$65.00", return the sum of the QTY columns associated with that row.


=SUMIF({Labor Invoice Tracking Range 3}, "Fabrication", IF({Labor Invoice Tracking Range 4}, "$65.00"), {Labor Invoice Tracking Range 2})

Smartsheet came up with "Labor Invoice Tracking Range 2" when I clicked on the column in the linked doc that I wanted to reference. Should it update the Range with the Column name?

For instance: =SUMIF({Labor Invoice Tracking Range Labor}, "Fabrication", IF({Labor Invoice Tracking Range Rate}, "$65.00"), {Labor Invoice Tracking Range QTY})

This still gives me back and error.

Sorry, I know that's a lot! It seems like it would be all the same style of formula.

Thank you for your help!

Best Answer


  • THANK YOU! That was a huge help, and solved my problem! Thank you for taking the time to explain this!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!