Excel SUMIFS: Build a Month-Wise Expense Review
A practical guide to excel sumifs, with a worked example, evidence checklist, common mistakes and steps to prepare a defensible working.
AI Summary
A register has 300 invoices across departments, dates and expense heads. The useful result is a working that explains the facts, the calculation or classification, and the evidence behind the conclusion. This guide shows how to prepare that working and where a reviewer should investigate before accepting the result.
Scope and applicable period
Practical software and accounts workflow. Features depend on the actual product, edition, subscription and version. The example illustrates a control or computation, not certification of an installation.
Verification date: 11 October 2026. Figures and rates identified as assumptions are teaching examples; apply the stated conditions and the actual facts to a real assignment.
The key principle
SUMIFS can aggregate a value column using multiple conditions, such as expense category and date boundaries. The sum and criteria ranges must have compatible dimensions. Use genuine Excel dates and a start-inclusive, next-period-start-exclusive approach so that transactions with time components are handled consistently. A formula that returns a number still needs reconciliation to the source population.
Worked example
| Item | Value or fact | What it means |
|---|---|---|
| Category | Rent | Controlled text value |
| April transaction | ₹30,000 | Included in April |
| May transaction | ₹30,000 | Excluded from April |
| Undated transaction | ₹5,000 | Exception, not silently allocated |
With amounts in C2:C100, categories in B2:B100, dates in A2:A100 and the month start in E2, use =SUMIFS(C2:C100,B2:B100,"Rent",A2:A100,">="&E2,A2:A100,"<"&EDATE(E2,1)). Confirm that E2 is a real first-of-month date. The April result is ₹30,000 in this example; the ₹5,000 undated item belongs in an exception list. Compare totals by category and period with the original ledger, including reversals and credits. Avoid fixed ranges that omit new rows.
A practical sequence
Convert dates and amounts to reliable data types and agree category labels. Set the month start and exclusive next-month boundary, then build SUMIFS with matching ranges.
Compare the resulting monthly totals with the full ledger and isolate blank dates or unmatched categories. Test a transaction on each boundary and a credit entry.
Keep the original input beside the reviewed output so that formula results can be checked against actual rows.
Validate the output as well as the operation
Software can organise or calculate data quickly, but its result depends on the input population, configuration and the user’s decisions. Retain the original source, test a few known records and reconcile control totals after import or transformation. Check version support before distributing formulas or prescribing menu commands. Review missing, duplicated and unusually changed records rather than focusing only on a successful operation. When a workflow requires approval, access control or retention, confirm the actual product capability and supplement it with a documented team procedure where needed. A technically successful import is not the final accounting review.
Evidence checklist
Keep the following records linked to the same entity, period and working version. Identify missing items explicitly; a checked box should mean the document was examined and supports the stated conclusion.
- Source table and date types
- Category master
- Formula range review
- Undated and unmatched exceptions
- Total reconciliation
Common mistakes and how to avoid them
- Using text dates without testing. Compare the conclusion with the source table and date types and resolve any conflicting facts.
- Summing ranges of different sizes. Trace the affected item to the formula range review before finalising the working.
- Ignoring transactions below the final formula row. Use the total reconciliation to make the final position and remaining exceptions clear.
Before you finalise
Recheck the example’s assumptions against the actual assignment, resolve the identified exceptions and make the final figure or conclusion traceable to its source. Preserve the reviewed version and the reason for material changes. For this task, the total reconciliation should agree with the conclusion presented to the client, reviewer or authority.
Frequently asked question
Why can an expense be missing from the result? Check its date, category spelling, data type and whether the row is inside every formula range.
Sources and further reading
Related guide: Excel power query accountants register reconciliation.