Help with a SUMIF formula 🙋‍♂️

Mark Silvester

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

Paul Newcome

It looks like you are just missing a square bracket after the second Spare Qty column reference...

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

Paul Newcome

It looks like you are just missing a square bracket after the second Spare Qty column reference...

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