# SUMIF formula confusion // help?

Options

I am reaching out as I am getting a little confused with a SUMIF problem. I've included a screenshot below to help illustrate.

So, the blue parent rows represent delivery phases and the device list at the top is the total contracted device count we get from a client.

The goal is that when a person enters a shipping quantity in the delivery phases below, the top total count adjusts accordingly and is represented in the "Remaining Quantity" column.

Using "Device SKU" as the unique identifier and subtracting from the "Quantity" column, I know this is possible... but just can't seem to make it work.

If anyone has any advice it would be greatly appreciated!

• Employee
Options

Hi Mark,

Since your chart is in the same sheet, the SUMIF formula will SUM your total quantities at the top. To work around this, you will need to use the SUMIF function, then minus that quantity @row, and finally minus that whole calculation from the quantity @ row.

Try this:

=Quantity@row - (SUMIF([Device SKU]:[Device SKU], [Device SKU]@row, Quantity:Quantity) - Quantity@row)

To break it down, here's just the SUMIF:

SUMIF([Device SKU]:[Device SKU], [Device SKU]@row, Quantity:Quantity)

This looks for the SKU number in that row, then SUMS all the values in the Quantity column, if that Device number matches (including the total number).

Then we minus the total number: - Quantity@row

And finally we take that entire calculation, and minus that number from the Quantity @row:

Quantity@row - (SUMIF(etc...

Let me know if this works for you!

Cheers,

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

Success!

Thank you for helping me wrap my head around this one Genevieve. It is very much appreciated.

Cheers,

Mark

• Employee
Options

Haha awesome! Happy to help!

October 8 - 10, Seattle, WA | Register now

• ✭✭
Options

Is there a way to do the same referencing another sheet? Lets just say for example I have an "Inventory Sheet", and I want to subtract the Quantity from a "Inventory Pull Sheet" by Matching the skus?

This is the Inventory Sheet where I would like the subtraction to take place.

And this is the sheet I would like to reference the "quantity" to be subtracted by its respected sku.

• Employee
Options

Hi @Jimmy

It looks like Paul has provided you a solution on this other post, here: https://community.smartsheet.com/discussion/82033/inventory-automation-how-to-subtract-cell-data-from-one-sheet-to-another-by-matching-sku#latest

Feel free to tag me on that other post if you still have questions!

Cheers,

Genevieve