Help with a SUMIF formula 🙋‍♂️

Mark Silvester
edited 05/12/20 in Formulas and Functions

I am having trouble with a SUMIF formula that is intended to calculate "returns" in the shipping/inventory database below:



My intention is that when a number is entered in the "Spare Qty" column underneath the "Returned" cell in the blue parent row for a specific SKU, that the "Returned" column in the far right automatically calculates and the "Remaining" column next to it updates as well.


The formula I have plugged in with no success is:


=(SUMIF(SKU:SKU, SKU@row, [Spare Qty]:[Spare Qty) - [Spare Qty]@row)


In this case, the 29 that is added for SKU 14314 underneath "Returned" in the blue parent row would ideally have populated above in the "Remaining" and in the "Returned" columns. I.e. For SKU 14314, it would be 29 in the returned column and 0 in the remaining column.


I also have other formula's at work in the spreadsheet that are more or less doing the same SUMIF equation but when I am trying to replicate it, it is unparseable. For example, when a number is added in the "In-Unit Count" column under the "Qty" cell in the blue parent row, the "Shipped" column and the "Remaining" column update correctly.


The formula at work in the "Shipped" column is:


=(SUMIF(SKU:SKU, SKU@row, [In-Unit Count]:[In-Unit Count]) - [In-Unit Count]@row)


The formula at work in the "Remaining" column is:


=([In-Unit Count]@row - (SUMIF(SKU:SKU, SKU@row, [In-Unit Count]:[In-Unit Count]) - [In-Unit Count]@row) + [Spare Qty]@row)


I might've been staring at this too long, but if anyone could offer some advice, it would be greatly appreciated!


Thanks,

Mark

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!