SUMIFS

You have the journal lines of Veldhuis Installatie BV from the XAF auditfile. The trial balance says account 4500 (Huisvestingskosten) has a debit total for the year, and you want to recalculate it from the lines. Type the account number in F1 and the total appears.

Formula in F2

=SUMIFS(C2:C7, B2:B7, F1)

=SUMIFS(C2:C7; B2:B7; F1)

Result: €6,445.00€ 6.445,00

Watch out: Amounts that the export stored as text are skipped without a warning, so the total comes out lower than the trial balance. If the difference is unexplained, check the column with ISNUMBER.

For the cut-off test you need the total of the purchase journal for December 2025. The list has postings on both sides of the month, including one on 31 December.

You type the first day of the month in F1 and the formula works out the window, so for the January test you change one cell.

Formula in F2

=SUMIFS(C2:C8, B2:B8, ">="&F1, B2:B8, "<="&EOMONTH(F1, 0))

=SUMIFS(C2:C8; B2:B8; ">="&F1; B2:B8; "<="&EOMONTH(F1; 0))

Result: €7,135.00€ 7.135,00

Watch out: If the dates carry a time, a posting at 14:30 on 31 December is larger than 46022 and fails <=. Use "<"&EOMONTH(F1, 0) + 1 to include the whole last day.

=SUMIFS(C2:C8, B2:B8, ">="&F1, B2:B8, "<"&EOMONTH(F1, 0) + 1)

=SUMIFS(C2:C8; B2:B8; ">="&F1; B2:B8; "<"&EOMONTH(F1; 0) + 1)

For journal entry testing you look at manual postings: entry numbers starting with MEM- come from the memorial journal. You want one figure for manual postings on the debiteuren (1300) and crediteuren (1600) accounts together. SUMIFS joins conditions with AND, so '1300 or 1600' needs a different construction.

Formula in G3

=SUM(SUMIFS(C2:C9, A2:A9, G1:G2, B2:B9, "MEM-*"))

=SUM(SUMIFS(C2:C9; A2:A9; G1:G2; B2:B9; "MEM-*"))

Result: €8,555.00€ 8.555,00

Watch out: If G1 and G2 hold the same account, the rows are counted twice and the total is inflated with no warning. The OR trick only works for values that exclude each other.

=SUMIFS(C2:C9, A2:A9, G1, B2:B9, "MEM-*") + SUMIFS(C2:C9, A2:A9, G2, B2:B9, "MEM-*")

=SUMIFS(C2:C9; A2:A9; G1; B2:B9; "MEM-*") + SUMIFS(C2:C9; A2:A9; G2; B2:B9; "MEM-*")

=SUMPRODUCT(ISNUMBER(MATCH(A2:A9, G1:G2, 0)) * (LEFT(B2:B9, 4) = "MEM-") * C2:C9)

=SUMPRODUCT(ISNUMBER(MATCH(A2:A9; G1:G2; 0)) * (LEFT(B2:B9; 4) = "MEM-") * C2:C9)

You want to reconcile the fourth quarter to the monthly movements in the client's trial balance. A small grid of postings per account per month next to the journal lines does that, without a PivotTable that someone forgets to refresh.

The accounts run down E2:E3 and the first day of each month runs across F1:H1. One formula in F2 should fill the whole grid.

Formula in F2

=LET(
  dates, A2:A9,
  accounts, B2:B9,
  amounts, C2:C9,
  starts, F1:H1,
  ends, DATE(YEAR(starts), MONTH(starts) + 1, 1),
  SUMIFS(amounts, accounts, E2:E3, dates, ">="&starts, dates, "<"&ends)
)

=LET(
  dates; A2:A9;
  accounts; B2:B9;
  amounts; C2:C9;
  starts; F1:H1;
  ends; DATE(YEAR(starts); MONTH(starts) + 1; 1);
  SUMIFS(amounts; accounts; E2:E3; dates; ">="&starts; dates; "<"&ends)
)

Result: €1,650.00 | €960.00 | €1,580.00 / €870.00 | €1,845.00 | €720.00€ 1.650,00 | € 960,00 | € 1.580,00 / € 870,00 | € 1.845,00 | € 720,00

Watch out: The mirror-image mistake is "<="&ends. Then 1 December counts in both November and December: account 4000 in November becomes 960 + 1580 = 2540, and the grid total exceeds the list total by 1580. The control sum in the last step is how you catch it.

=SUMIFS($C$2:$C$9, $B$2:$B$9, $E2, $A$2:$A$9, ">="&F$1, $A$2:$A$9, "<"&EOMONTH(F$1, 0) + 1)

=SUMIFS($C$2:$C$9; $B$2:$B$9; $E2; $A$2:$A$9; ">="&F$1; $A$2:$A$9; "<"&EOMONTH(F$1; 0) + 1)