How to Use SUM in Excel
Adds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
What is SUM in Microsoft Excel?
SUM adds every numeric value you give it and ignores text, blanks and logical values inside ranges. It accepts up to 255 arguments, and those arguments can be individual cells, ranges, whole columns or the results of other formulas.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| number1 | Required | The first number, cell reference or range to add. |
| number2 | Optional | Additional numbers or ranges. Up to 255 arguments are allowed in total. |
When to Use SUM (Real-World Business Scenarios)
- 1
Totalling a sales, expense or attendance column at the bottom of a table.
- 2
Adding several non-adjacent ranges at once, such as =SUM(B2:B10,D2:D10,F2:F10).
- 3
Summing a whole column with =SUM(B:B) when the sheet grows every month and you do not want to edit ranges.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Month | Sales | Target |
|---|---|---|
| Jan | 45000 | 40000 |
| Feb | 52000 | 45000 |
| Mar | 38000 | 45000 |
How it works: Adds January to March sales. SUM skips any text or blank cells inside B2:B4, so the total stays correct even if a row is empty.
Common Errors & How to Fix Them
Why it happens: The numbers are stored as text, usually after a CSV import. SUM ignores text values inside ranges.
Fix: Check for the green error triangle, then use Data > Text to Columns or VALUE() to convert the column to real numbers.
Why it happens: A referenced sheet or row was deleted, leaving a broken range in the formula.
Fix: Press Ctrl + Z to restore the deleted cells, then re-point the range to the surviving data.
Try SUM Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (SUM)
What does the SUM formula do in Excel?
SUM adds every numeric value you give it and ignores text, blanks and logical values inside ranges. It accepts up to 255 arguments, and those arguments can be individual cells, ranges, whole columns or the results of other formulas.
What is the syntax for SUM?
What are common mistakes when using SUM?
Total is 0 or too low: The numbers are stored as text, usually after a CSV import. SUM ignores text values inside ranges. (Check for the green error triangle, then use Data > Text to Columns or VALUE() to convert the column to real numbers.) #REF!: A referenced sheet or row was deleted, leaving a broken range in the formula. (Press Ctrl + Z to restore the deleted cells, then re-point the range to the surviving data.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- COUNTIFCounts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
- MAXReturns the largest number in a range — highest sale, top score, latest date or most recent invoice.
Master SUM in Basic Excel
Learn SUM along with our formula library, practice files, and verifiable certificates in our complete curriculum.