A small contracting crew with fuel cards and a supply-store account can flag out-of-policy expenses before month end by catching two things automatically: possible duplicate charges and missing receipts. Both can be done with one formula each. The amounts below are in Canadian dollars and invented. The rest is a weekly ten-minute check, not a monthly surprise.
What goes wrong on a crew
Three people use the same card at the same supply store in one morning. One buys screws for C$38.40, another is sent back for the same screws and buys them again. A receipt blows away in a truck. Neither is dishonest, and both cost money that is hard to explain at the end of the month.
Set up the log
- A: date. B: who bought. C: supplier. D: amount. E: job name. F: receipt photo Yes or No.
Formula one: possible duplicates
In column G, enter =IF(COUNTIFS($A$2:$A$500,A2,$C$2:$C$500,C2,$D$2:$D$500,D2)>1,"Possible duplicate",""). It counts rows with the same date, supplier and amount, and flags a row if more than one exists. It works in Excel and Google Sheets. It will sometimes flag two legitimate purchases of the same item, which is why it says possible.
Formula two: missing receipts
In column H, enter =IF(F2<>"Yes","Receipt missing",""). Then at the top of the sheet, =COUNTIF(H2:H500,"Receipt missing") shows how many are outstanding. Treat anything missing after three working days as a conversation, not a mystery.
An example morning
| Who | Supplier | Amount | Flag |
|---|---|---|---|
| Sam | Supply store | C$38.40 | Possible duplicate |
| Priya | Supply store | C$38.40 | Possible duplicate |
| Sam | Fuel | C$91.15 | Receipt missing |
| Priya | Lumber yard | C$212.60 | None |
| Total | All rows | C$380.55 | 3 flags |
Check: 38.40 plus 38.40 is 76.80, plus 91.15 is 167.95, plus 212.60 gives C$380.55. A phone call settles the first two: they bought the same screws, and one set goes back for a refund. Without the formula, nobody would look.
How do we handle the refund?
Enter it as a negative line on the day it happens, with the same supplier and a note: returned screws. Your total then reflects real spending, and the duplicate no longer stands out. A refund that arrives as a credit on the next statement can be matched to that line.
What if a flagged charge is wrong, not just odd?
Sometimes a duplicate is a supplier error. Keep the statement line and the receipt, contact the supplier with the date, amount and job, and write the request date in a notes column. When the credit arrives, enter it as a negative line and mark the flag as resolved. Do not delete the original charge, since it is the evidence that a mistake happened and was fixed.
It is worth checking the card statement itself, not just your sheet, at least once a month. The sheet shows what the crew recorded; the statement shows what was actually charged. Any statement line with no matching row in the sheet is a purchase nobody logged, and that is a different problem from a duplicate.
A ten-minute Friday check
- Sort by the flag columns.
- Ask for missing receipts.
- Review each possible duplicate.
- Enter refunds.
- Reset the counts for next week.
SpendixAI has a report that lists same-amount, same-merchant charges within two days for imported statements, but it is built for India and INR and is not a crew or approvals tool, so this sheet is the fit for a Canadian crew.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


