Running into negative inventory in Google Sheets is more common than you might think. One of the main reasons? Entering sales quantities that are bigger than what you actually have in stock.
The good news is: with data validation, you can easily make sure you never sell more than what’s available, saving time and preventing errors. This trick is especially handy for traders, wholesalers, and inventory managers.
Why You Should Limit Sales Quantity
Let’s say you have this situation:
- Available stock for Item 1: 100 MT
- Customer A orders: 60 MT
- Customer B orders: 50 MT
If you enter both orders as-is, the total (110 MT) goes over your stock—and suddenly, you have negative inventory.
With data validation, Google Sheets can automatically restrict the second order to only 40 MT, keeping your total at 100 MT.
When you have multiple items and customers, manually tracking stock gets messy. This method keeps everything accurate without constantly checking your numbers.
Setting Up Data Validation in Google Sheets
Here’s the setup we’ll use:
- Stock Table (Sheet1) – where all purchases are recorded
- Sales Table (Sheet2) – where sales orders are entered
Our goal: limit sales entries in the Sales Table based on available stock in the Stock Table.
Stock Table Examples
Note: Whether an item has a single stock entry or multiple entries, the validation rule works the same way.
Sales Table Example
For example, the total stock of Road Base (RB01) is 150 MT. The rule will prevent Customer B from entering more than 80 MT (150 – 70).
The Data Validation Formula
Here’s the custom formula to make this work:
=SUMIF($A$2:$A, A2, $C$2:$C) <= SUMIF(Sheet1!$A$2:$A, A2, Sheet1!$C$2:$C)How to Apply It
- Select the Quantity column in your Sales Table (e.g., C2:C).
- Go to Data → Data Validation.
- Click Add Rule.
- Under Criteria, choose Custom formula is.
- Paste the formula above.
- Select Reject input for invalid entries.
- Click Done.
Now, any attempt to enter a quantity that exceeds stock will be blocked automatically.
How This Formula Works
The formula uses two SUMIF functions:
SUMIF($A$2:$A, A2, $C$2:$C)→ sums all sales quantities for the item.SUMIF(Sheet1!$A$2:$A, A2, Sheet1!$C$2:$C)→ sums all available stock for the same item.
It only allows an entry if total sales ≤ stock.
Relative references ensure it works for each row automatically.
Benefits of Using Data Validation
- Prevents negative inventory automatically
- Reduces errors when entering sales data
- Works for items with single or multiple stock entries
- Easy to maintain for multiple products and customers
