Decide what one row means
Separate order totals from line items before counting or adding anything.
| Order | Line | Line amount | Invoice amount |
|---|---|---|---|
| A101 | 1 | 120 | 200 |
| A101 | 2 | 80 | 200 |
A repeated ID is not automatically a duplicate
Order A101 can contain two legitimate line items. Its order ID repeats because the table stores lines, not orders. Removing one line would lose a real amount. Before cleaning duplicates, write down the entity represented by a row and the fields that identify it.
Match the measure to the grain
The example has two lines worth 120 and 80. Their total is 200. The invoice amount is repeated on both lines for context, so adding that column gives 400 and counts the invoice twice. Neither a formula nor a PivotTable can choose the intended business meaning for you.
Count rows when you need the number of lines. Count distinct order IDs when you need orders. To total an invoice-level measure, first build one row per invoice using a rule that handles amendments and missing values.
Test a small reconciliation
Choose one order whose expected total you know. Confirm the line total, invoice total and record count separately. Then check a second order with a different number of lines. A report that agrees only on single-line orders can hide a grain error.
Check the idea
What is the recorded line-amount total for A101?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.