Written with AI assistance. The formulas below were checked in LibreOffice Calc on sample rows (not in Excel or Google Sheets).
Most wrong spreadsheet totals aren't formula bugs. They're data bugs: the same order typed twice, "Asha Traders" in one row and "Asha Trader" in the next, or a stray space at the end of a name. Your per-customer totals then quietly split in two, and nothing turns red.
Here are three checks you can add to any order list. They use only standard functions that exist in both Excel and Google Sheets (TRIM, COUNTIF, SUMPRODUCT, IF), with no macros.
1. The order list
One row per order, starting at row 5:
| Column | What goes in it |
|---|---|
| A | Order ID |
| B | Order date |
| C | Customer (as typed) |
| D | Amount |
| E | Customer, cleaned (formula) |
| F | Repeat ID (formula) |
| G | Order key (formula) |
| H | Possible duplicate (formula) |
| I | Name check (formula) |
Put your list of real customer names in K5:K20, spelled once, correctly.
2. Clean the name: TRIM
=IF(A5="","",TRIM(C5))
TRIM removes spaces at the start and end and squeezes double spaces in the middle down to one. So " Chandra Stores " becomes "Chandra Stores" and "Bose & Co" becomes "Bose & Co". Point every total or lookup at column E, never at the raw column C.
3. Same order ID twice
=IF(A5="","",IF(COUNTIF($A$5:A5,A5)>1,"Repeat ID",""))
The trick is the range $A$5:A5: the start is locked and the end grows as you fill down. Each row counts only the rows above and including itself, so the first ORD-202 stays clean and only the second one is flagged. You know which row to look at, instead of both rows lighting up.
4. Same order typed twice under different IDs
Sometimes the ID is different but it's clearly the same order: same customer, same day, same amount. Build a key (G5):
=IF(A5="","",E5&"|"&B5&"|"&D5)
The date joins as its underlying number (e.g. 46266), which is fine for matching. Then flag the repeat (H5):
=IF(A5="","",IF(SUMPRODUCT(--($G$5:G5=G5))>1,"Possible duplicate",""))
Why SUMPRODUCT instead of COUNTIF here? COUNTIF treats * and ? in the text as wildcards, and long text can trip it up. A plain = comparison avoids both. It's also case-insensitive, so asha traders and Asha Traders with the same date and amount get flagged.
It says possible on purpose. Two genuine orders can match, so treat the flag as "look at this", not "delete this". Same customer and same day but a different amount is not flagged.
5. Names that aren't on your list
=IF(A5="","",IF(COUNTIF($K$5:$K$20,E5)=0,"Not in list",""))
This catches "Asha Trader" (missing s) before it becomes a separate customer in your totals.
Better still, stop typos at the door with a dropdown on column C:
-
Excel: select the column → Data → Data Validation → Allow: List → Source
=$K$5:$K$20. -
Google Sheets: select the column → Data → Data validation → Add rule → Dropdown (from a range) →
K5:K20.
Keep the "Not in list" check anyway. It catches anything pasted in, and rows typed before the dropdown existed.
6. A small summary and one check cell
| Cell | Formula |
|---|---|
| Repeat IDs | =COUNTIF($F$5:$F$104,"Repeat ID") |
| Possible duplicates | =COUNTIF($H$5:$H$104,"Possible duplicate") |
| Names not in list | =COUNTIF($I$5:$I$104,"Not in list") |
| Total, all rows | =SUM($D$5:$D$104) |
| Total, flagged rows left out | =SUMPRODUCT($D$5:$D$104*($F$5:$F$104="")*($H$5:$H$104="")) |
| Check (N10) | =IF(N5+N6+N7=0,"OK","CHECK") |
(N5, N6 and N7 are the first three cells.) The two totals side by side show how much money is sitting in suspicious rows.
On my ten sample orders, each check caught exactly the row it should:
- Repeat IDs: 1
- Possible duplicates: 1
- Names not in list: 1
- Total of all rows: 6,950; with the flagged rows left out: 5,450
- The check cell showed CHECK
Your numbers will differ. What matters is that each planted problem gets caught and nothing else does.
7. Test it with planted mistakes
Before trusting it, add rows you know are wrong and confirm each gets flagged:
| Planted row | Expected |
|---|---|
| Name with extra spaces | cleaned, no flag |
| Same order ID twice | second one: Repeat ID |
| Same customer, date and amount, different capitals | second one: Possible duplicate |
| Same customer and date, different amount | no flag |
| Misspelled customer | Not in list |
| Completely blank row | stays blank |
Then delete the planted rows and fill the formulas down 100 rows in advance.
Prefer a ready-made file? There's a free Order & Payment Tracker (.xlsx workbook, no macros). You log each order once, and it works out what's still owed and whether it's overdue, by customer and in a one-page summary: https://ashishsrivastavajay.gumroad.com/l/free-order-payment-tracker
About me, and a disclosure: I'm Ashish, based in Kolkata, with 18+ years of work experience, and I write these tutorials and build my workbooks with AI assistance under my direction. The free tracker is mine.
Top comments (0)