Math
Subtotal by invoice number on first row only (Prevent double-counting fees)
Show one merchandise subtotal and one repeated invoice-level freight charge per invoice, with an explicit consistency check.
Stop double-counting repeated order-level shipping and discounts in ERP exports!
Some exports repeat an invoice-level freight amount on each line. This sample assumes that layout. Other exports allocate freight per line; deduplicating those would undercount charges. Confirm the source schema first.
The first-occurrence rule shows each invoice subtotal only once. It requires nonempty invoice IDs, numeric amounts and consistent freight within each invoice. If freight differs across lines, the example flags the inconsistency instead of silently choosing the first value. IDs in this example contain no COUNTIF/SUMIF wildcard characters.
| Invoice (A) | SKU Line $ (B) | Repeated Freight (C) | Deduped Freight (D) & Subtotal (E) |
|---|---|---|---|
| INV-501 (Line 1) | $120 | $25 | Freight = $25 | InvSubtotal = $300 |
| INV-501 (Line 2) | $180 | $25 (Duplicate!) | Freight = $0 | InvSubtotal = $0 |
| INV-502 (Line 1) | $95 | $15 | Freight = $15 | InvSubtotal = $95 |
| INV-503 (Lines 1–2) | $210 + $140 | $30 repeated x2 | Freight = $30 | InvSubtotal = $350 |
Output invoice line-item subtotal only on the first row of each invoice
Summing E2:E6 equals the exact $745 merchandise total without any double-counting.
=IF(COUNTBLANK(A2)>0,"Missing invoice ID",IF(COUNTIF($A$2:A2,A2)=1,SUMIF($A$2:$A$6,A2,$B$2:$B$6),0))Deduplicate repeated order-level freight across multi-line invoices
For the consistent sample, raw freight sums to 125 while one charge per invoice sums to 70. The guarded total returns Review invoice data for missing/nonnumeric entries or conflicting repeated freight. A plain SUM would ignore text warnings and could show a misleading partial total.
=IF(OR(COUNT(B2:C6)<>10,COUNTBLANK(A2:A6)>0,COUNTIF(D2:D6,"Conflicting freight")>0),"Review invoice data",SUM(D2:D6))Try the example
Use the original values or change them to test your understanding.