I’m seeking guidance on how to set up a formula in Google Sheets that performs vertical subtraction depending on specific conditions. My spreadsheet contains data like SKU numbers, inventory balances, customer IDs, and order quantities. I would like each order to be deducted from the overall balance as I progress through the rows, engaging with matches to the SKU.
| SKU |
SKU BALANCE |
ID CUST |
CUST ORDER |
DESIRED FORMULA |
| 10100 |
100 |
115501 |
90 |
10 |
| 10100 |
100 |
115502 |
10 |
0 |
| 10036 |
100 |
115503 |
25 |
75 |
| 10036 |
100 |
115504 |
25 |
50 |
| 10036 |
100 |
115505 |
5 |
45 |
| 10040 |
100 |
115506 |
50 |
50 |
| 10040 |
100 |
115507 |
25 |
25 |
| 10040 |
100 |
115508 |
30 |
-5 |
| 10041 |
100 |
115519 |
50 |
DUPLICATES |
| 10041 |
100 |
115519 |
50 |
DUPLICATES |
| 10041 |
100 |
115519 |
50 |
DUPLICATES |
| 10036 |
100 |
115520 |
45 |
0 |
It’s important that when a new SKU is introduced, it resets to the original available stock and adjusts the orders sequentially. In cases where orders surpass the available stock, I need to see negative balances highlighted. Additionally, it’s crucial to account for any duplicate customer orders accurately. Can anyone suggest a fitting formula for this functionality?
I ran into the same issue with warehouse inventory data. The trick is getting the reset logic right when you hit a new SKU. I used a nested approach with XLOOKUP or INDEX/MATCH to grab the original balance dynamically. Here’s the formula that worked: =IF(COUNTIF($A$1:A2,A2)=1,B2-D2,INDEX(E$1:E1,MATCH(TRUE,($A$1:A1=A2)*($A$1:A1<>$A$2:A2),0))-D2). The COUNTIF part catches the first time each SKU appears and resets to the original balance. For other rows with the same SKU, it finds the last calculated balance for that group. For duplicates, add a validation step with COUNTIFS to spot identical customer-SKU combos before processing. This stops you from double-counting orders from the same customer. Negative values show up naturally when orders go over available stock - makes it easy to catch oversold situations.
This is tricky - you need running balances per SKU. I’ve handled similar inventory tracking before and had good luck combining SUMIFS with IF functions. Try this in your formula column: =IF(A2<>A1,B2-D2,E1-D2) where E is your formula column. It checks if the current SKU is different from the previous row. If yes, it starts fresh with original balance minus current order. Same SKU? It takes the previous balance and subtracts the current order. For duplicates, you’ll probably need a helper column with COUNTIFS to flag when the same customer ID shows up multiple times for one SKU. The formula handles negatives automatically - just keeps subtracting past zero. Start your formula on row 2 since it references the previous row.
Honestly, I’d just use a helper column here. Try =B2-SUMIF($A$2:A2,A2,$D$2:D2) - it grabs the original balance and subtracts all orders for that SKU up to the current row. Handles resets when new SKUs show up and does the running totals automatically without getting messy.