The SUM function
Excel's most used function. It adds up everything you give it, except what is not a real number, and it does not say so.
Syntax
=SUM(number1, [number2], …)
In a French Excel: =SOMME(nombre1; [nombre2]; …).
Arguments
- number1, number2…: numbers, cells or ranges, up to 255.
An example
=SUM(B2:B100,D2:D100)
Adds up both columns; empty cells and text are ignored.
Pitfalls
- Numbers stored as text are ignored: the total is wrong with no error. Convert text to numbers fixes them.
- Hidden rows are counted; to ignore them, SUBTOTAL.
Going further
- SUMIF and SUMIFS, to add up by criteria.
- Guide: convert text to numbers.
Frequently asked questions
- Why does SUM return 0?
- The cells contain numbers stored as text (left-aligned, often with a green triangle). SUM ignores them.