Nested SUMIF formulas across multiple sheets

Colleen Marshall

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

Labor Rate QTY SUBTOTALS

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.

This says "INCORRECT ARGUMENT".

=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!

Ben Goldblatt

Hi Colleen,

I'm happy to help out with your formulas but feel free to also reach out to the Support Team whenever you're running into technical issues with formulas or any Smartsheet features. You can click here to create a Support ticket if needed.

For #1, it sounds like you're looking for a way to have a formula create a dropdown option. As you found, you can populate specific values based on certain criteria but it's not currently possible to generate a list of dropdown options when formula conditions are met. I would definitely Submit a Product Enhancement for this to be considered as a possibility for future development.

For #2, if the goal is to SUM the "SUBTOTALS" values when "Labor" is "Fabrication" and "Rate" is $45.00, you should be able to accomplish this using the SUMIFS function, which allows for calculations based off of multiple specified criteria:

 =SUMIFS(SUBTOTALS:SUBTOTALS, Labor:Labor, "Fabrication", Rate:Rate, 45)

For #3, you should also be able to use a SUMIFS formula to SUM the "Quantity" values in your source sheet based off of the Labor name and hourly rate. The Sheet reference name is optional but I recommend naming them according to what you're referencing:

• Range 1 (the group of cells to SUM) = Quantity
• Range 2 (criterion range for the Labor name) = Labor
• Range 3 (criterion range for the Hourly Rate) = Rate

With the Labor names and Rate values already listed in your destination sheet, you can use @row references instead of typing in the text values, allowing you to use the same formula on each row:

 =SUMIFS({Quantity}, {Labor}, [Category and Item]@row, {Rate}, Hourly@row)

For more information on the SUMIFS function, @row references, and working with cross-sheet formulas, visit the following Help articles:

• SUMIFS Function
• Use @row to Improve Formula Performance
• Formulas: Reference Data from Other Sheets

I hope this helps!

Ben

Ben Goldblatt

Hi Colleen,

I'm happy to help out with your formulas but feel free to also reach out to the Support Team whenever you're running into technical issues with formulas or any Smartsheet features. You can click here to create a Support ticket if needed.

For #1, it sounds like you're looking for a way to have a formula create a dropdown option. As you found, you can populate specific values based on certain criteria but it's not currently possible to generate a list of dropdown options when formula conditions are met. I would definitely Submit a Product Enhancement for this to be considered as a possibility for future development.

For #2, if the goal is to SUM the "SUBTOTALS" values when "Labor" is "Fabrication" and "Rate" is $45.00, you should be able to accomplish this using the SUMIFS function, which allows for calculations based off of multiple specified criteria:

 =SUMIFS(SUBTOTALS:SUBTOTALS, Labor:Labor, "Fabrication", Rate:Rate, 45)

For #3, you should also be able to use a SUMIFS formula to SUM the "Quantity" values in your source sheet based off of the Labor name and hourly rate. The Sheet reference name is optional but I recommend naming them according to what you're referencing:

• Range 1 (the group of cells to SUM) = Quantity
• Range 2 (criterion range for the Labor name) = Labor
• Range 3 (criterion range for the Hourly Rate) = Rate

With the Labor names and Rate values already listed in your destination sheet, you can use @row references instead of typing in the text values, allowing you to use the same formula on each row:

 =SUMIFS({Quantity}, {Labor}, [Category and Item]@row, {Rate}, Hourly@row)

For more information on the SUMIFS function, @row references, and working with cross-sheet formulas, visit the following Help articles:

• SUMIFS Function
• Use @row to Improve Formula Performance
• Formulas: Reference Data from Other Sheets

I hope this helps!

Ben

Colleen Marshall

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