Data
Beginner
16 min
Formulas — describe what you want in English, get the formula, verify it
The problem
You need a lookup that returns the most recent price for a product from a price-history table, but only for the customer's own price band. You know exactly what you want. You spend forty minutes on forums, assemble something from three answers, and it returns #N/A on 200 of 4,000 rows for reasons you cannot diagnose.
The workflow
1State the rule
2Give real columns
3Give sample rows
4Specify no-match behaviour
5Get formula and explanation
6Test three rows
7Apply
★Document it
What you need
Version: Excel 365. Sheet 'Orders', data from row 2, headers row 1.
The tool
Any assistant
The prompt
Subscribers. The full prompt is part of the SMEMinds AI Playbook. See plans.
What comes back
1. THE FORMULA =SUMIFS(F:F, B:B, G2, E:E, "Delivered", A:A, ">="&TODAY()-90) 2.
Verify before you use it
Human review required. Check every line below before this leaves your screen.
- Test the three rows before dragging down. Specifically test one Cancelled order and confirm it is excluded.
- Check that column A contains real dates, not text. The edge-case note flags this as returning 0 silently — the worst kind of failure.
- Verify the total against a manual sum of a small customer. Two minutes, and it catches a wrong column reference immediately.
- If the sheet has other Status values you did not mention — 'Returned', 'Partially delivered' — decide explicitly how they should be treated.