SUMPRODUCT

You are auditing Veldhuis Installatie BV. The inventory list (voorraadlijst) at 31 December 2025 shows a quantity and a cost price per article, and the ledger shows account 3000 Voorraad at 9,800.009.800,00. You want the value of the list in one cell, without adding a helper column of line values first, so you can explain the difference.

Formula in E2

=SUMPRODUCT(B2:B5, C2:C5)

=SUMPRODUCT(B2:B5; C2:C5)

Result: €9,739.50€ 9.739,50

Watch out: Select C2:C6 by accident and the ranges no longer match: the result is #VALUE!, not a wrong total. That error is SUMPRODUCT's size check doing its job.

=SUM(B2:B5 * C2:C5)

=SUM(B2:B5 * C2:C5)

You recalculate the VAT (btw) on the sales and purchase journals of an XAF export: a net amount and the VAT rate of each line. You want the recalculated VAT per journal next to a short list of journal codes, with one formula copied down, and compare it with the VAT return (btw-aangifte).

One line without a VAT code has a hyphen in the rate column, and that must not break the total.

Formula in F2

=SUMPRODUCT(--($A$2:$A$7 = E2), $B$2:$B$7, $C$2:$C$7)

=SUMPRODUCT(--($A$2:$A$7 = E2); $B$2:$B$7; $C$2:$C$7)

Result: €282.00€ 282,00

Watch out: Treating text as 0 also means that a VAT rate imported as text, '0.21 with an apostrophe, silently drops out of the recalculation. If the recalculated VAT looks low, check the rate column with ISNUMBER.

=SUMPRODUCT(($A$2:$A$7 = E2) * 1, $B$2:$B$7, $C$2:$C$7)

=SUMPRODUCT(($A$2:$A$7 = E2) * 1; $B$2:$B$7; $C$2:$C$7)

In journal entry testing you want the total of postings on account 8000 Omzet that come from the manual (MEM) or bank (BNK) journal, because revenue normally comes from the sales journal VRK.

SUMIFS combines its criteria with AND only, so 'MEM or BNK, and account 8000' needs either two SUMIFS added up or one SUMPRODUCT that adds the two journal tests. The journals and the account sit in input cells so you can change them.

Formula in G4

=SUMPRODUCT(((B2:B8 = G1) + (B2:B8 = G2)) * (C2:C8 = G3), D2:D8)

=SUMPRODUCT(((B2:B8 = G1) + (B2:B8 = G2)) * (C2:C8 = G3); D2:D8)

Result: €8,800.00€ 8.800,00

Watch out: Type MEM in G2 as well and the two tests overlap: MEM rows get 1 + 1 = 2, and the result silently becomes 2 × 7,0507.050 = 14,10014.100 instead of 7,0507.050. Wrap the OR part in ((...) > 0) whenever the terms can both be true, for example when they test different columns.

=SUMIFS(D2:D8, B2:B8, G1, C2:C8, G3) + SUMIFS(D2:D8, B2:B8, G2, C2:C8, G3)

=SUMIFS(D2:D8; B2:B8; G1; C2:C8; G3) + SUMIFS(D2:D8; B2:B8; G2; C2:C8; G3)

=SUMPRODUCT((((B2:B8 = G1) + (B2:B8 = G2)) > 0) * (C2:C8 = G3), D2:D8)

=SUMPRODUCT((((B2:B8 = G1) + (B2:B8 = G2)) > 0) * (C2:C8 = G3); D2:D8)

From the receivables list (debiteurenlijst) you want the average number of days a customer takes to pay, weighted by invoice amount so a large invoice counts more than a small one.

Invoices that are still open have no payment date yet, so their blank days must not count as 0, and a customer with no paid invoices must show a message instead of #DIV/0!. The formula uses LET so each step has a name.

Formula in F2

=LET(
  keep, (A2:A8 = F1) * ISNUMBER(C2:C8),
  weights, keep * B2:B8,
  points, SUMPRODUCT(weights, C2:C8),
  IF(SUM(weights) = 0, "No paid invoices", points / SUM(weights))
)

=LET(
  keep; (A2:A8 = F1) * ISNUMBER(C2:C8);
  weights; keep * B2:B8;
  points; SUMPRODUCT(weights; C2:C8);
  IF(SUM(weights) = 0; "No paid invoices"; points / SUM(weights))
)

Result: 43.8343,83

Watch out: The mirror-image mistake is the unweighted average: SUMPRODUCT(keep, C2:C8) / SUM(keep) gives (30 + 60 + 20) / 3 = 36.67. It also uses the right rows and looks equally reasonable, but an invoice of 10,00010.000 and one of 25,00025.000 now count the same.

=SUMPRODUCT((A2:A8 = F1) * ISNUMBER(C2:C8), B2:B8, C2:C8) / SUMPRODUCT((A2:A8 = F1) * ISNUMBER(C2:C8), B2:B8)

=SUMPRODUCT((A2:A8 = F1) * ISNUMBER(C2:C8); B2:B8; C2:C8) / SUMPRODUCT((A2:A8 = F1) * ISNUMBER(C2:C8); B2:B8)