ElyxAI

Goods receipt and picking in Excel: the quantity gap

Warehouse SupervisorGoods Receipt and Picking

A warehouse floor workbook answers two quantity questions, on two sheets. On Receipts, each row is one purchase-order line: the quantity the order asked for, and the quantity you counted when the goods arrived. On Picks, each row is one order line to take from a location: the quantity to pick, and the quantity you actually picked. The difference on the row is the gap. A negative gap is short. A positive gap on a receipt is more than the order. These sheets do not say where a truck is, and they do not tell you when to reorder. A promise date from a carrier is a different list. A minimum stock level is a different list. Here you are standing at the dock or at the pick face with a count. SUMIFS totals the quantity received for one order number, so you can see the order as a whole after you have checked the lines. IF flags a line where the counted quantity is not the ordered quantity. COUNTIFS counts how many lines are still short. You do not hide a short line by editing the ordered quantity to match what arrived. The ordered quantity stays what the order said. The counted quantity stays what you counted. Before you trust a gap, check that both quantities are numbers. A number stored as text makes the difference look wrong or makes SUMIFS skip the row. Sort the quantity column and look for blanks. A blank is not zero. Decide whether a missing count means "not yet counted" (leave it blank, and do not treat the gap as short) or "counted as zero" (type 0). Write that rule on the sheet. Check three lines against the paperwork in your hand before you put the goods away or before you close the pick. If those three gaps match what you see, the column is safe to read. Elyx can write the difference and the totals on the sheets you already use. You still count. The trial lasts 7 days, with no card.

The Problem

Receipts go wrong when the counted quantity is typed over the ordered quantity. The row then looks perfect and the shortage is gone. Keep both numbers. The gap formula subtracts one from the other. If someone must correct a count, they correct the counted cell and they say so in a note. They do not change the order. Picks go wrong when the picked quantity is copied from the quantity to pick without a count. The sheet then says the line is complete because two cells show the same formula, not because someone counted the cart. The picked quantity is an entry. The quantity to pick is an entry or a lookup from the order. The gap compares them. Putting receipts and picks on one table fails in a different way. A receipt line and a pick line are not the same event. One order number can appear on both sheets for different reasons. Two sheets keep the dock count separate from the pick count, and the short-line count on each sheet means one thing.

Benefits

Ordered quantity and counted quantity stay in two columns.

The gap is counted minus ordered on a receipt, and picked minus required on a pick.

SUMIFS totals received quantity for one order without adding the pick sheet.

COUNTIFS counts lines where the gap is not zero.

A blank count stays blank so an uncounted line is not treated as zero.

Step-by-Step Tutorial

1

Build the receipt sheet

Name a sheet Receipts. Row 1 headers: Order, Line, Item, Ordered, Counted, Gap, Note. Order is the purchase order number. Line is the line on that order. Item is the code you actually use on the dock. Ordered is the quantity on the order. Counted is the quantity you counted. Gap is a formula. Note is where you write a broken carton or a substitution, in words. Do not merge the header. Format Ordered, Counted, and Gap as numbers.

If the same order has many lines, repeat the order number on every line. Do not type it only on the first line and leave the rest blank. SUMIFS cannot see a blank.

2

Write the receipt gap

With Ordered in D2 and Counted in E2, the gap is =IF(E2="","",E2-D2). The blank test matters. If Counted is empty, the gap stays empty, which means not yet counted. If you omit the test, an empty counted cell is treated as zero and every new line looks short. A negative gap means you received less than the order. A positive gap means you received more. Zero means the count matches the order. Do not use ABS. The sign is the information.

=IF(E2="","",E2-D2)

Copy the formula down the column, or use a table so new rows inherit it. Do not paste values over it.

3

Total one order

On the same sheet, or in a small block to the right, total Ordered and Counted for the order number you type in I2. Ordered total: =SUMIFS(D:D,A:A,I2). Counted total: =SUMIFS(E:E,A:A,I2). The difference of those two totals should equal =SUMIFS(F:F,A:A,I2) once every line of that order has been counted. If the counted total is blank-heavy, SUMIFS ignores text but it also ignores blank cells, so an uncounted line is missing from the total. That is a reason to finish the count before you read the order total. Do not SUM the whole Gap column if other orders are on the sheet.

=SUMIFS(E:E,A:A,I2)

Start ranges at row 2 if you ever put a number in the header. Headers here are words, so the full column is safe.

4

Count the short receipt lines

A short line is a gap below zero. =COUNTIFS(F:F,"<0") counts them. A line not yet counted has a blank gap and is not in this count. If you also want the uncounted lines, count blanks in Counted: =COUNTIFS(E:E,""). Subtract 1 if the header cell is inside the range and is blank, or start at E2. Read both numbers. Short lines are work on the dock. Blank lines are lines you have not touched. They are not the same pile.

=COUNTIFS(F:F,"<0")

Conditional formatting on Gap less than 0 makes the short lines visible without reading the count.

5

Build the pick sheet

Name a second sheet Picks. Headers: Order, Line, Item, Need, Picked, Gap, Location, Note. Need is the quantity the order line asks you to take. Picked is what you counted into the carton or onto the pallet. Location is the bin you took it from, typed, not calculated. The gap formula is the same shape as the receipt: =IF(E2="","",E2-D2), with Need in D and Picked in E. A negative gap means the pick is short. Do not copy the receipt rows onto this sheet. A receipt is what arrived. A pick is what left a location.

=IF(E2="","",E2-D2)

Use the same item codes on both sheets. The code is how a person finds the row. It is not a formula.

6

Flag a pick that is not complete

Beside the gap, a status word helps the filter. =IF(E2="","open",IF(F2=0,"done","short")) with Picked in E and Gap in F. Open means not counted yet. Done means the gap is zero. Short means counted and not equal. Filter Status to short when you are clearing the remaining lines. Do not type the status by hand or it will drift from the quantities. If you need to override, change the quantity and let the status follow.

=IF(E2="","open",IF(F2=0,"done","short"))

COUNTIFS on that status column tells you how many pick lines are still short: =COUNTIFS(H:H,"short").

7

Look up the ordered quantity instead of retyping it

If the order lines already live on a sheet named Orders, with the order number in column A, the line in column B, and the quantity in column D, you can pull Ordered onto Receipts. A simple way, when each order-and-line pair is unique, is XLOOKUP on a key you build. In a helper column, =A2&"|"&B2 builds the key on both sheets. Then =XLOOKUP(G2,Orders!G:G,Orders!D:D,"missing") returns the quantity or the word missing. If it says missing, do not invent a quantity. Find the line. Retyping from memory is how the ordered quantity starts to match the count.

=XLOOKUP(G2,Orders!G:G,Orders!D:D,"missing")

The key separator should be a character that is not in your order numbers. A vertical bar is usually safe.

8

Check three lines against the paper

On Receipts, pick three lines you just counted. The Counted cell must be the number you wrote on the paperwork. The Ordered cell must be the number on the order, not the number you wished had arrived. The gap must be the subtraction. On Picks, do the same with Need and Picked. If one line fails, fix the cell before you put stock away or before you say the order is picked. The SUMIFS total is only as good as those cells. The sheet does not know what was on the pallet.

Write the counter's initials in Note if two people count the same line. Do not add a second counted column unless you really have a recount.

Template Features

Gap that stays blank until you count

If the counted or picked cell is empty, the gap is empty. An empty cell is not a shortage.

=IF(E2="","",E2-D2)

Order total

SUMIFS adds counted quantity for one order number. It does not add the pick sheet.

=SUMIFS(E:E,A:A,I2)

Short-line count

COUNTIFS counts gaps below zero. Those are the lines still short after a count.

=COUNTIFS(F:F,"<0")

Pick status from the quantities

Open, done, or short comes from the picked quantity and the gap. You do not type the word.

=IF(E2="","open",IF(F2=0,"done","short"))

Lookup of the ordered quantity

XLOOKUP reads the quantity from the order sheet when the order and line match. A missing line returns the word missing.

=XLOOKUP(G2,Orders!G:G,Orders!D:D,"missing")

Concrete Examples

A short receipt you can subtract by hand

One line of a purchase order arrives short. You want the gap to stay negative after the goods are booked.

Order PO-18, line 2, item A14. Ordered: 20. Counted: 18.

Result: Gap is 18 minus 20, which is -2. COUNTIFS of gaps below zero includes this row. SUMIFS of Counted for PO-18 includes 18, not 20. Do not change Ordered to 18.

A line you have not counted yet

The order has a second line still on the truck. You do not want it to look short.

Ordered: 10. Counted: empty.

Result: Gap is blank. The short-line count ignores it. The blank count on Counted includes it. Finish the count, then read the order total.

A pick that matches

You picked a line in full and you want the status to say done without typing the word.

Need: 6. Picked: 6. Location: B12.

Result: Gap is 0. Status is done. A filter on short hides this row. Location stays B12 because you typed it. The sheet did not choose the bin.

Pro Tips

Do not receive and pick in the same row

A row is one event. If the same item is received and later picked, those are two rows on two sheets. Linking them with a lookup is fine. Overwriting the receipt count with the pick count destroys the dock record.

Units belong in the header

Write the unit once, in the header or in a cell named Unit: pieces, cases, or kilos. A row of cases added to a row of pieces will still add. The formula cannot see the unit. Keep one unit per sheet.

Recounts go in Note, then in Counted

If you recount, type the new count in Counted and write the old count in Note. Do not keep two live count columns unless the second one is the recount you will switch to on purpose.

Close the filter when you leave

A filter that hides short lines makes the next shift think the dock is clear. Clear the filter, or leave it on short and say so in a cell at the top of the sheet.

Formulas Used

Don’t want to do it by hand? Ask Elyx

Example request

“Build this in my workbook: Warehouse goods receipt and picking, with the formulas, totals and formatting”

Elyx does it directly in your Excel file.

7 days free · No credit card required

Related guides

Frequently Asked Questions