Statistical
Compare two columns row by row: count matches, mismatches, and accuracy %
Audit parallel Ordered vs Shipped or Forecast vs Actual columns row-for-row using SUMPRODUCT without helper columns.
Row-by-row positional match vs anywhere-in-list match
COUNTIF checks whether a value appears ANYWHERE in another column—which misses swapped rows when auditing Ordered Qty vs Shipped Qty line-for-line.
Comparing B2:B5 = C2:C5 inside SUMPRODUCT(--(B2:B5 = C2:C5)) compares row 2 only against row 2, row 3 against row 3, and totals exact positional matches.
| SKU (A) | Ordered (B) | Shipped (C) | Positional Match? |
|---|---|---|---|
| Item-1 | 25 | 25 | TRUE (1) |
| Item-2 | 40 | 38 | FALSE (0 — short-shipped) |
| Item-3 | 10 | 10 | TRUE (1) |
| Item-4 | 15 | 15 | TRUE (1) |
Positional match count, mismatch count, and accuracy %
Divide exact matches by ROWS(B2:B5) to report line-item fulfillment accuracy (3 / 4 = 0.75).
=SUMPRODUCT(--(B2:B5 = C2:C5))Count row-level discrepancies
Use <> to count lines requiring warehouse investigation.
=SUMPRODUCT(--(B2:B5 <> C2:C5))Inputs and interpretation
Both ranges must have the same shape and refer to the same records in the same order. This example assumes every quantity is populated and numeric; two blank cells can otherwise count as a match. Exact equality is case-insensitive for text and can be unsuitable for floating-point measurements; define a tolerance when needed. The percentage measures row agreement, not overall shipment quality or completeness.
Try the example
Use the original values or change them to test your understanding.