Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Math

SUM

SOM in Dutch Excel

Adds up numbers, cells and ranges, skipping anything in a range that is not a real number.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C7 holds the formula
Row	A	B	C
1	Entry no.	Description	Debit
2	25-0101	Rent January	€4,250.00€ 4.250,00
3	25-0102	Service charges	€612.50€ 612,50
4	25-0103	Cleaning	€385.75€ 385,75
5	25-0104	Energy	€1,148.20€ 1.148,20
6	25-0105	Building insurance	€527.30€ 527,30
7	Total account 4500		€6,923.75€ 6.923,75=SUM(C2:C6)=SUM(C2:C6)

Result: €6,923.75€ 6.923,75

=SUM(C2:C6)

=SUM(C2:C6)

What SUM does and when to use it

What it does: it adds up everything you give it and returns one number, for example a column of ledger amounts.

When to use it: for any total without conditions. It replaces chains such as =B2+B3+B4 and keeps working when you insert a row inside the range. For conditions use SUMIFS; for a filtered list use SUBTOTAL.

How Excel evaluates it

Excel reads the arguments from left to right. For each range or reference it looks at every cell: numbers are added, while blanks, text and TRUE or FALSE are skipped, and that includes numbers stored as text.

For a value typed directly into the formula the rules are looser:

SUM("5", 2) converts the text to 5 and returns 7
TRUE counts as 1
text that cannot be read as a number, such as SUM("abc"), returns #VALUE!

If any cell in a range holds an error such as #N/A, the whole SUM returns that error. The + operator behaves differently from SUM: it converts a text number in a cell and fails with #VALUE! on real text.

Watch out: SUM skips numbers stored as text in a range without any error or warning.

Mental model

A cashier counting the till who only picks up coins: anything else in the drawer, including a note that says '45.80', is left where it lies.

Terms used on this page: operator, reference, error value, argument, range

Syntax

Official SUM documentation

SUM(number1, [number2], ...)

SUM(number1; [number2]; ...)

Arguments
Argument	Type	Meaning
`number1`	Required	The first number, cell or range to add.
`number2`	Optional	More numbers, cells or ranges, up to 255 arguments in total.

Return value:A number: the total of the arguments

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 17 KB)

Beginner

Total a ledger account and compare it with the trial balance

You are testing account 4500 Huisvestingskosten for Veldhuis Installatie BV. The auditfile extract lists five debit lines for the account. You want the total of those lines in one cell, so you can compare it with the balance on the client's saldibalans.

Sheet excerpt: C7 holds the formula
Row	A	B	C
1	Entry no.	Description	Debit
2	25-0101	Rent January	€4,250.00€ 4.250,00
3	25-0102	Service charges	€612.50€ 612,50
4	25-0103	Cleaning	€385.75€ 385,75
5	25-0104	Energy	€1,148.20€ 1.148,20
6	25-0105	Building insurance	€527.30€ 527,30
7	Total account 4500		€6,923.75€ 6.923,75=SUM(C2:C6)=SUM(C2:C6)
8

Formula cell (C7)Input

Formula in C7

=SUM(C2:C6)

=SUM(C2:C6)

Result: €6,923.75€ 6.923,75

Step by step

Excel reads the single argument, the range C2:C6, and looks at its five cells from top to bottom.
All five hold numbers, so all five are added: 4,250.004.250,00 + 612.50 = 4,862.504.862,50.
4,862.504.862,50 + 385.75 = 5,248.255.248,25, and 5,248.255.248,25 + 1,148.201.148,20 = 6,396.456.396,45.
6,396.456.396,45 + 527.30 = 6,923.756.923,75. That number lands in C7, and you can set it against the saldibalans.
Change the 385.75 in C4 to 358.75 (two digits swapped) and C7 updates to 6,896.756.896,75 on its own; the formula stores the instruction, not the answer.

Watch out: Select C7 and press Alt+= and Excel proposes a SUM for you; check the range it guessed before pressing Enter, because it stops at the first blank cell it meets. In a large auditfile extract that can leave out most of the lines.

Bank receipts with an amount stored as text

You copied five customer receipts from the bank statement export into your workpaper. The total is lower than the statement shows: the receipt from D1002 came in as text, and SUM leaves it out without saying so.

You want a total that includes it while you ask the client for a clean export.

Sheet excerpt: B7 holds the formula
Row	A	B
1	Relation	Amount
2	D1001	€12,400.00€ 12.400,00
3	D1002	3210.50
4	D1003	€7,850.00€ 7.850,00
5	D1004	€1,975.25€ 1.975,25
6	D1005	€4,620.75€ 4.620,75
7	Total receipts	€30,056.50€ 30.056,50=SUM(VALUE(B2:B6))=SUM(VALUE(B2:B6))
8

Formula cell (B7)InputNote: B3 holds the text 3210.50, not a number: it is left-aligned and shows a green triangle. The other amounts are real numbers.

Formula in B7

=SUM(VALUE(B2:B6))

=SUM(VALUE(B2:B6))

Result: €30,056.50€ 30.056,50

Step by step

First see what a bare =SUM(B2:B6) does: it adds the four real numbers, 12,400.0012.400,00 + 7,850.007.850,00 + 1,975.251.975,25 + 4,620.754.620,75 = 26,846.0026.846,00, and skips B3 because it is text. No error, no warning.
In the formula above, Excel evaluates VALUE(B2:B6) first. VALUE works on each of the five cells and returns an array of five numbers: 12400, 3210.5, 7850, 1975.25 and 4620.75. The text in B3 is now the number 3210.5.
SUM receives that array instead of the range. An array produced by a calculation holds only numbers here, so all five are added: 26,846.0026.846,00 + 3,210.503.210,50 = 30,056.5030.056,50.
Compare =B2+B3+B4+B5+B6. The + operator also converts the text in B3, so it returns 30,056.5030.056,50 as well. That is why a hand-built chain and a SUM of the same cells can disagree.
Convert B3 to a real number and a bare =SUM(B2:B6) gives 30,056.5030.056,50 too. The VALUE wrapper is a bridge, not a replacement for clean data.

Watch out: If any cell holds real text, such as n/a, VALUE returns #VALUE! for it and the whole total becomes #VALUE!. The bare SUM would have skipped it and shown 26,846.0026.846,00 without complaint.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

SUM skips text only when it finds it in a range. VALUE turns every cell into a number before SUM sees it, so there is no text left to skip. The contrast with + shows the underlying rule: operators convert, SUM over a range does not.

More to watch out for

VALUE reads text with the decimal separator of your regional settings. A bank file from a system that writes 3210.50 opened in Dutch Excel is not read as 3210,50; NUMBERVALUE with an explicit separator is safer for such files.
A blank cell in B2:B6 becomes 0 through VALUE, which is harmless for a total but matters when you reuse the construction for an average.

Variations

=SUMPRODUCT(--B2:B6)

=SUMPRODUCT(--B2:B6)

The double minus converts text numbers the same way and SUMPRODUCT adds the result without Ctrl+Shift+Enter, so this works in Excel 2019 and earlier too.

AdvancedExcel 2021 or later (This example needs Excel 2021 or later)

Inventory value per category from quantity times cost

The client's inventory list (voorraadlijst) holds lines with a category, a quantity and a unit cost, but no column with the line value. You want to recalculate the value of the raw materials and compare it with the balance sheet.

SUMIFS cannot multiply two columns before adding, so you combine SUM with FILTER.

Sheet excerpt: F2 holds the formula
Row	A	B	C	E	F
1	Category	Quantity	Unit cost	Category	Raw materials
2	Raw materials	120120	€8.50€ 8,50	Value	€2,697.00€ 2.697,00=SUM(FILTER(B2:B8 * C2:C8, A2:A8 = F1, 0))=SUM(FILTER(B2:B8 * C2:C8; A2:A8 = F1; 0))
3	Finished goods	4040	€36.25€ 36,25
4	Raw materials	7575	€12.40€ 12,40
5	Packaging	500500	€0.65€ 0,65
6	Raw materials	3030	€24.90€ 24,90
7	Finished goods	1818	€52.00€ 52,00
8	Packaging	250250	€0.80€ 0,80
9

Formula cell (F2)Input

Formula in F2

=SUM(FILTER(B2:B8 * C2:C8, A2:A8 = F1, 0))

=SUM(FILTER(B2:B8 * C2:C8; A2:A8 = F1; 0))

Result: €2,697.00€ 2.697,00

Step by step

Excel evaluates the innermost part first. B2:B8 * C2:C8 multiplies row by row and gives seven line values:
- 120 × 8.50 = 1,020.001.020,00
- 40 × 36.25 = 1,450.001.450,00
- 75 × 12.40 = 930.00
- 500 × 0.65 = 325.00
- 30 × 24.90 = 747.00
- 18 × 52.00 = 936.00
- 250 × 0.80 = 200.00
Next, A2:A8 = F1 compares each category with Raw materials and gives TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE. Rows 2, 4 and 6 match.
FILTER keeps the line values where the test is TRUE and returns 1,020.001.020,00, 930.00 and 747.00. The third argument, 0, is what FILTER returns if no row matches.
SUM adds the three values: 1,020.001.020,00 + 930.00 + 747.00 = 2,697.002.697,00.
Change F1 to Finished goods and the result becomes 1,450.001.450,00 + 936.00 = 2,386.002.386,00; Packaging gives 325.00 + 200.00 = 525.00.

Watch out: A category exported as "Raw materials " with a trailing space fails the comparison, and that line drops out of the total without any error. Clean the column with TRIM or use a drop-down list.

More on this example: why it works, 1 more pitfall, 2 variations

Why it works

SUM does not care whether its argument is a range or an array that a calculation produced, so you can shape the numbers first and add them last. FILTER does the selecting and the multiplication happens before it, which is exactly the step SUMIFS cannot do because it only accepts ranges that already exist on the sheet.

More to watch out for

The 0 for no match also hides a typo in F1: Raw material returns 0.00, which reads as 'no inventory'. Leave the third argument out while testing, so a typo shows #CALC! instead.

Variations

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

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

The same 2,697.002.697,00 without FILTER: TRUE and FALSE become 1 and 0 in the multiplication. Works in every Excel version.

=SUM(B2:B8 * C2:C8)

=SUM(B2:B8 * C2:C8)

Total inventory value over all categories, 5,608.005.608,00, to compare with the balance sheet total when you need no condition at all.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Checked grand total under a spilled breakdown

You analyse the manual memorial entries (journal MEM) of the auditfile. E2 lists the accounts with UNIQUE and F2 totals the amounts per account with SUMIFS, both as spilled ranges.

You need a grand total that follows the breakdown when an account is added, and that refuses to show a number if the breakdown no longer covers every posting in the extract.

Sheet excerpt: H2 holds the formula
Row	A	B	C	E	F	H
1	Account	Entry no.	Amount	Account	Amount	Total (checked)
2	40004000	25-0801	€6,200.50€ 6.200,50	40004000	10290.7510290,75	€16,357.00€ 16.357,00=LET( split, F2#, control, SUM(C:C), total, SUM(split), IF(ROUND(total - control, 2) = 0, total, "Check breakdown") )=LET( split; F2#; control; SUM(C:C); total; SUM(split); IF(ROUND(total - control; 2) = 0; total; "Check breakdown") )
3	45004500	25-0802	€3,150.00€ 3.150,00	45004500	3560.753560,75
4	40004000	25-0803	€1,840.25€ 1.840,25	80008000	2505.52505,5
5	80008000	25-0804	€925.00€ 925,00
6	45004500	25-0805	€410.75€ 410,75
7	40004000	25-0806	€2,250.00€ 2.250,00
8	80008000	25-0807	€1,580.50€ 1.580,50
9

Formula cell (H2)InputNote: E2 holds =UNIQUE(A2:A8) and F2 holds =SUMIFS(C2:C8, A2:A8, E2#). Both spill three rows down; the values shown in E2:F4 are their results. The total sits in H2, beside the report, so the spill can grow downward without hitting it.

Formula in H2

=LET(
  split, F2#,
  control, SUM(C:C),
  total, SUM(split),
  IF(ROUND(total - control, 2) = 0, total, "Check breakdown")
)

=LET(
  split; F2#;
  control; SUM(C:C);
  total; SUM(split);
  IF(ROUND(total - control; 2) = 0; total; "Check breakdown")
)

Result: €16,357.00€ 16.357,00

Step by step

LET names the pieces. split is F2#: the # tells Excel to take the whole spill that starts in F2, which today is F2:F4 with 10,290.7510.290,75, 3,560.753.560,75 and 2,505.502.505,50.
control is SUM(C:C), the whole column. The header text in C1 is skipped because SUM ignores text in a range, and the seven amounts are added: 6,200.506.200,50 + 3,150.003.150,00 + 1,840.251.840,25 + 925.00 + 410.75 + 2,250.002.250,00 + 1,580.501.580,50 = 16,357.0016.357,00.
total is SUM(split): 10,290.7510.290,75 + 3,560.753.560,75 + 2,505.502.505,50 = 16,357.0016.357,00.
The test rounds the difference to two decimals first: ROUND(16,357.0016.357,00 - 16,357.0016.357,00, 2) = 0. Rounding matters because two sums of decimal numbers added in a different order can differ by something like 0.000000000000004, and a bare total = control would then be FALSE.
The test is TRUE, so IF returns total: 16,357.0016.357,00 lands in H2.
Now the extract gains a posting of 1,200.001.200,00 on a new account 4800 in row 9. control becomes 17,557.0017.557,00, but UNIQUE in E2 still reads A2:A8, so the breakdown and split stay at 16,357.0016.357,00. The difference is -1,200.001.200,00 and H2 shows Check breakdown instead of a number that looks right.
Extend E2 and F2 to row 9 and the spill grows to row 5. F2# follows on its own, total becomes 17,557.0017.557,00, equal to control, and the number returns.

Watch out: The mirror-image mistake is SUM(F2:F4) instead of SUM(F2#). It returns 16,357.0016.357,00 today, and after the breakdown grows to four accounts it still adds only the first three, without any error.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

The # reference makes the total as dynamic as the list it adds, so there is no fixed range to forget. The control total comes from a different route through the same data, and comparing the two after rounding turns a silent gap into a visible message.

Do not use this construction when you only need the grand total and show no breakdown: SUM(C:C) on its own is clearer. The alternative is a Table with a Total Row, which grows automatically and needs no formula, but cannot hold spilled formulas and gives you no reconciliation.

More to watch out for

Placing the total directly under the spill, in F5, works until a fourth account appears; then F2 returns #SPILL! because F5 is in the way, and the whole report goes blank.
Never put this total in column C itself: SUM(C:C) would then include its own cell and Excel reports a circular reference.

Variations

=SUM(F2#)

=SUM(F2#)

The bare dynamic total, without the control check, for a report nobody reconciles.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Total lower than the amounts you see	A total is lower than the figures you see, because some amounts were imported as text. They are left-aligned and often carry a small green triangle.	Select the cells and choose Convert to Number from the warning icon, or use Data > Text to Columns > Finish. Check with COUNT, which counts only real numbers.
Circular reference warning	The total sits inside its own range, for example =SUM(B2:B7) in B7, and Excel warns about a circular reference.	End the range one row above the total: =SUM(B2:B6).
SUM ignores the filter	A filter is on and the SUM under the list still shows the grand total of every row.	Use SUBTOTAL(109, B2:B6) for totals that should follow the filter.
New rows missing from the total	New rows are typed below the range and the total does not include them.	Convert the list to a Table (Ctrl+T) and sum the column by name, or insert new rows above the last row of the range so it stretches.

Used in combinations

Debtor age analysis table that ties to the control accountTotal the open amount per age band with one SUMIFS over a band table, add the bands up with SUM and check that the total agrees with the control account.
Year-end cut-off test with FILTER and SUMFlag the sales invoices whose booking date and delivery date fall on different sides of year end and total them: two comparisons with the year-end date give TRUE or FALSE per date, FILTER keeps the invoices where the two differ, and SUM adds them up.Excel 2021 or later (This combination needs Excel 2021 or later)
Receivables aging total per 30-day bandTurn every due date into a band with XLOOKUP and match_mode -1 on a band table, then let SUM add up the open amounts of one band.Excel 2021 or later (This combination needs Excel 2021 or later)
Two-way lookup with one XLOOKUP and TOCOLFind the value where a row label and a column header meet, by turning both conditions into one grid of 0s and 1s and flattening it with TOCOL.Excel 2024 or later (This combination needs Excel 2024 or later)