Stockout list in Excel: the count against the minimum
A store stockout list is one row per product. You type a code, the name the floor already uses, the quantity you counted, and the minimum you chose. The flag is a word: out, low, or ok. Out means the count is zero. Low means the count is above zero and under the minimum. Ok means the count is at or above the minimum. COUNTIFS counts the rows whose flag is out, and a second COUNTIFS counts the rows whose flag is low. This list is not a receiving log. A receiving log is one line per delivery, and a product can have many lines. If you put those deliveries on this list, one product becomes many rows and you can no longer answer "is this product out on the shelf" by reading a single count. Keep the latest count here. Keep the cartons in the log. When a delivery changes what is on the shelf, count again, or add the quantity you accepted to the count you already trust, and type the new number in the same row. The sheet does not know tomorrow's sales. You type the minimum. IF compares the count with zero and with that minimum. It does not forecast. If you want a higher minimum before a busy weekend, change the number in the cell. Do not hide a second rule inside the formula. A blank count is not ok. Blank means you have not typed a count. Zero means you counted and the shelf is empty. Those are different facts, and the flag treats them differently. A blank stays blank. A zero becomes out. Before you call a supplier, read three products you can see. The count on the sheet must match the shelf, or the last count sheet you are willing to stand behind. The minimum must be the number you meant, not a leftover from another season. Elyx can write the flag and the counts. You still type the quantity and the minimum. The trial lasts 7 days, with no card.
The Problem
The list fails when the count is a sentence. "Low, waiting on Tuesday's delivery" cannot be compared with a minimum. Put the sentence in a note. Put a number in the count. The flag reads the number. It also fails when each morning starts a new row for the same product. You then have five counts for one code and the latest one is whichever row you happen to see. One product, one row. You edit the count in place. The note can say what changed. A third failure is copying a warehouse on-hand figure onto the shop list and treating it as the shelf. The warehouse number answers a different question: what is in the building, not what a customer can take from this aisle. If you sell from the shelf, the count on this list is the shelf. If you also keep a warehouse sheet, leave it there. Do not average the two. A fourth failure is using the minimum as a promise about the supplier. The minimum is a number you typed. The flag does not place an order, and it does not know a lead time. If you want a note that a replenishment was asked for, type it in the note. Do not change the count to the quantity you hope will arrive.
Benefits
One row per product, edited in place when you recount.
Out means zero. Low means under the minimum and still above zero.
A blank count stays blank, so an uncounted product is not marked ok.
COUNTIFS counts out and low as two numbers, not one blended total.
Deliveries stay on a receipt sheet so the list does not grow a row per carton.
Step-by-Step Tutorial
Lay out one row per product
Name the sheet Floor. Headers in row 1: SKU, Product, OnHand, Min, Flag, Note. SKU is the code your till or your shelf label already uses, and it is unique in this store. Product is the name staff say out loud. OnHand and Min are numbers you type. Flag is a formula. Note is text. One product occupies one row for as long as you sell it in this store. Add a row when a product joins the range. Do not add a row because a new day started, and do not add a row because a carton arrived. If the same code is sold in two stores, this file is one store. A second store is a second sheet, or a column you filter, not a second row that you forget to filter.
Sort by SKU and scan for duplicates before you rely on the count. Two rows for one code will be counted twice.
Type the count you are willing to stand behind
OnHand is a number: the quantity you counted on the shelf, or the quantity you updated after a delivery you checked. Leave it blank if you have not counted and you are not willing to reuse an older number. Do not type a guess to make the column look full. Do not type text such as "n/a" or "low". Format the column as a number. A count stored as text will not compare with zero the way you expect, and the flag will not match the word you think you should see. If a product is sold by weight, decide the unit once, write it in the product name or in the note, and type the count in that unit on every row. Do not mix packs and pieces in the same column.
If two people count the same product, agree who types the number. The sheet keeps the last number saved. It does not keep both counts.
Type the minimum as a number you chose
Min is the level below which you want the row marked low, while the count is still above zero. Type it as a number. A minimum of zero means only a count of zero will look wrong, because low requires the count to be under the minimum and above zero. If you do not want a low flag for a product, type a minimum of zero and let out be the only alarm, or leave Min blank and accept that the low test will not fire until you type one. Do not type the minimum as a sentence such as "one case". A case is a number of units if you have decided how many units are in a case. Write that decision in the note if people will forget it. The formula will not convert cases for you.
Change Min in the cell when the season changes. Do not keep last winter's minimum on a summer product and then distrust the flag.
Flag out, low, or ok
With OnHand in C2 and Min in D2, the flag is =IF(C2="","",IF(C2<=0,"out",IF(D2="","",IF(C2<D2,"low","ok")))). A blank count stays blank. A count of zero or below is out. If the count is positive and Min is blank, the flag stays blank, because you have not typed the level you care about. If the count is positive and under Min, the flag is low. If the count is positive and at or above Min, the flag is ok. Negative counts become out. A negative count usually means a typing error or a return you have not finished. Fix the number. Do not build a fourth word for negatives unless you also change the counts that read the flag.
=IF(C2="","",IF(C2<=0,"out",IF(D2="","",IF(C2<D2,"low","ok"))))Copy the formula down the rows you use. A row with a count and no formula will be missing from both counts.
Count what is out and what is low
Out, for this list, means the flag is the word out. =COUNTIFS(E:E,"out") is that count. Low is =COUNTIFS(E:E,"low"). Read them as two numbers. The first is what a customer cannot buy. The second is what is still on the shelf but under the minimum you typed. Do not add them together and call the sum "stockouts". A low row still has stock. Put the two counts at the top of the sheet and label them in the next cell, in words, so a closer who did not build the file can read them. Ok rows are in neither count. Blank flags are in neither count. That is why an uncounted product does not quietly become ok.
=COUNTIFS(E:E,"out")If the out count looks too small, filter the flag column and look for typos such as "Out" with a capital, or "0" typed in the flag column by hand. The count reads the exact word.
Keep the words stable
The flag is a formula, so staff should not type over it. If you also want a status that people type, put that status in another column and do not use it for these counts. Protect the flag column if several people edit the file, or put the formula on a sheet they do not type on and point it at the typed columns. Data validation belongs on columns people type when the words matter. On this list the words that matter are produced by the formula, so the protection is "do not overwrite the formula". The SKU should still be unique. A simple way to see a repeated code is to count it: =COUNTIF(A:A,A2) next to a spare column, and look for anything above 1. That spare column is a check. It is not the stockout flag.
=COUNTIF(A:A,A2)Delete the duplicate check column when you are done, or leave it off to the right and hide it, so it is not read as a quantity.
Update the count when the shelf changes
When you recount, replace OnHand in the same row. When you accept a delivery onto the shelf, add the accepted quantity to the count you trust and type the new total, or recount and type what you see. Do not leave yesterday's zero in place because the carton is "in the back". If the carton is not on the shelf and not available to sell, it is not this count. The note can say the carton is in the back. Expectation is not a quantity. When you stop selling a product, cut the row to a sheet named Ended, or clear it from this list. Leaving a zero on a product you no longer sell keeps it in the out count forever.
Write the time of the count in the note if the shop is open while you count. The number is what you saw then, not a promise about the next hour.
Check three products before you publish the counts
Pick three rows. For each one, look at the shelf or the count sheet in your hand. OnHand must match. If Min is filled, it must be the minimum you meant. The flag must be out when the count is zero, low when the count is positive and under Min, ok when the count is at or above Min, and blank when OnHand is blank. If one row fails, do not send the out count. Fix the cell. The count is the count of the words on this sheet. It is not a statement about whether the range is large enough, and it is not an order. A receipt log can hold the deliveries. When the shelf changes, type the new count here. Do not paste the log's rows onto this list.
If a product is out on the shelf and available in the stockroom, the note says so. The flag still reads the shelf count you typed. Do not silently switch the meaning of OnHand halfway down the sheet.
Template Features
One current count
OnHand is the number you typed for this product. The flag reads it. Note holds the sentence that does not fit the number.
Out before low
Zero is out. A positive count under the minimum is low. The two words stay separate so the counts do not blend them.
=IF(C2="","",IF(C2<=0,"out",IF(D2="","",IF(C2<D2,"low","ok"))))Blank is not ok
An empty count does not become ok. You have not counted it. Fill the number when you have one you trust.
Two counts
One COUNTIFS counts out. Another counts low. Ok and blank stay out of both.
=COUNTIFS(E:E,"low")Duplicate code check
A spare COUNTIF shows whether the SKU appears more than once. Two rows for one code inflate the out count.
=COUNTIF(A:A,A2)Concrete Examples
A product you counted at zero
You open the file before the shop opens. One product's shelf is empty. You want the list to say out, and you want the out count to include it.
OnHand: 0. Min: 4.
Result: Flag is out, because the count is zero. The out count includes the row. The low count does not. The minimum does not turn a zero into low. Zero is the stronger word, and it is the one a customer will feel.
A product under the minimum, still on the shelf
You counted two units. The minimum you typed is six. You do not want this called a stockout.
OnHand: 2. Min: 6.
Result: Flag is low. The low count includes the row. The out count does not. You can still sell the two units. The flag tells you the count is under the number you chose, which is a different decision from a hole in the shelf.
A product you have not counted
A new line has no quantity yet. You do not want it marked ok, and you do not want it marked out.
OnHand: empty. Min: 4.
Result: Flag is blank. Neither count includes the row. Fill OnHand when you have a count. Until then the list is honest about the gap. A blank is not a zero you forgot to type.
Pro Tips
SKU is the key
Staff may shorten a product's name. The code on the shelf label should be unique and stable. If you look a product up, look it up on that column.
The stockroom is a note until you change the rule
If OnHand means the shelf, keep it that way on every row. A stockroom quantity belongs in a note or in another column you do not feed to this flag.
Do not let a delivery overwrite a count you have not checked
Adding a carton quantity to a stale count produces a number nobody counted. Either recount, or add only to a count you still trust, and say which you did in the note.
Ended products leave the list
Cut a delisted product to a sheet named Ended. A zero left behind stays in the out count and makes the morning number a fiction.