SUMIFS is the workhorse of month-end Excel. Most reporting packs are built on it.
SUMIFS adds up a column where one or more conditions are true: for example, the amount column where the account is 6100 and the date falls in March. Put the conditions in cells rather than typing them into the formula, use date ranges with greater-than and less-than signs, and reconcile the total back to the trial balance.
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
The sum range comes first, then pairs of range and condition. Every range must be the same size.
With a ledger export in columns A to E (date, account, cost centre, entity, amount), a report with account codes down column H and month-start dates across row 1:
=SUMIFS($E:$E,$B:$B,$H2,$A:$A,">="&I$1,$A:$A,"<="&EOMONTH(I$1,0))
This totals the amount for the account in H2 between the first and last day of the month in I1. Copy it across and down to build the whole grid. The mixed references ($H2, I$1) let one formula fill the table.
Add more pairs:
=SUMIFS($E:$E,$B:$B,$H2,$C:$C,$G$1,$D:$D,$G$2,$A:$A,">="&I$1,$A:$A,"<="&EOMONTH(I$1,0))
with the cost centre in G1 and entity in G2. Changing those two cells re-runs the whole report.
If account codes are numbers, total a range such as all accounts from 6000 to 6999:
=SUMIFS($E:$E,$B:$B,">=6000",$B:$B,"<=6999")
If codes are text, use a wildcard instead: "6*" totals every account starting with 6. Wildcards only work on text, so check how your codes were exported.
=COUNT(E:E) against the row count.Whole-column references such as $E:$E pick up new rows automatically, which is convenient for monthly exports. On very large files they can slow calculation; an Excel Table with structured references gives the same convenience with less recalculation.
Add a check row: the total of the SUMIFS grid for the period should equal the total of the ledger export for the same period, and the ledger export should agree to the trial balance. Show the difference in a cell that turns red when it isn't zero. A report that doesn't reconcile to the trial balance shouldn't go into a month-end reporting pack.
The formula is the same everywhere. UK teams often report by financial year starting in April; set the month headers from your own year start. Use the account codes your own chart of accounts uses, not the examples above.
12 questions on how strong your close controls are. Free to use online.
The Month-End Close & Controls System tests the trial balance against the sum of reconciled balances, keeps every open item on one exception dashboard and blocks sign-off while exceptions remain open. Excel, no macros, one-time price.
SUMIF takes one condition and puts the sum range last. SUMIFS takes several conditions and puts the sum range first. SUMIFS covers everything SUMIF does, so many teams use it everywhere.
Usually because a condition matches nothing: a number stored as text, a trailing space, or a date stored as text. Test one condition at a time to find which.
Pivot tables are quicker to build; SUMIFS keeps a fixed layout that updates when the data changes and is easier to check. Many packs use both.
Last updated 04 November 2026. General guidance for finance teams; check it against your own policies, systems and local rules.