The site supply sheet shows extra stock on one order and shortages on two others. The orders go to separate sites, so the extra units do not fill either shortage. Calculate the gap for each order before adding them up.

Line up requests with their deliveries

Enter requested counts 18, 6, 12 and 5 in B2:B5 and delivered counts 14, 9, 10 and 5 in C2:C5. Check that each pair belongs to the same order ID. Then calculate the row-level shortage total.

=SUMPRODUCT((B2:B5>C2:C5)*(B2:B5-C2:C5))

A row audit shows requested and delivered counts for orders A through D and highlights deficits of four and two.

Example request and delivery pairs. Match the order IDs before calculating.

Check the result against the four rows

The comparison includes only rows where the request exceeds delivery. The shortfalls are 4, 0, 2 and 0, for a total of 6. Subtracting the two column totals instead gives 3 because it offsets the shortages with a surplus of 3.

A comparison card shows six units short by order and a net difference of three across all orders.

Example totals. The surplus hides three missing units when the counts are netted.

Label the two totals differently if you need both. If a delivery count is missing, decide whether it means no units arrived or the delivery has yet to be recorded before including that row.

Microsoft’s SUMPRODUCT reference.