SUMIF formula confusion // help?

Mark Silvester

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

Screen Shot 2020-02-26 at 10.34.05 AM.png

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!

Genevieve P.

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

Mark Silvester

Success!

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

Cheers,

Mark

Genevieve P.

Haha awesome! Happy to help!

Jimrny

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.

Genevieve P.

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