Excel Tips: The SUM Function

Gif showing how the SUM Function works

The SUM Function in Excel is useful for: quickly adding up numbers in a range of cells.

It’s a basic yet powerful tool that can handle everything from simple additions to complex calculations involving multiple data types.

Excel Function Details

SUM Function Syntax


Available in: All versions of Microsoft Excel
User Level: Beginners
Inputs: “range”: Cells to be summed.
“criteria”: Optional, defines conditions for summing.
“sum_range”: Optional, specific cells to sum if criteria met.
Output: Sum of selected cells. Data Type: Number
Wildcards: Supports “*” and “?”
Case Sensitive: No, treats similar texts the same

Where To Find The SUM Function

To find the COUNT function, go to the top of the ribbon on your screen. Click Formulas > Math & Trig, and scroll down to the SUM Function.

Looking for a different function? Read this article on the Average Function.

Using SUM With Other Data Types

SUM is a very precise function and will not work with data types other than numbers.

To get intended results you will need to use numbers. Check out the below example to see what happens when you use dates!

Date Data:

Excel treats dates as numbers. Because of this, you will get unintended outcomes when adding up dates.

Simple Example

Imagine you want to add up your daily expenses. Select the cells with your expense amounts, then use the SUM function:


This formula adds up the values from cell A2 to A7.

Advanced Example

Let’s combine SUM with IF to sum only certain expenses. Suppose you only want to add expenses over £50:


This formula sums values in A2:A5 that are greater than $50.

Troubleshooting & Errors

Let’s take a look at some common errors you may run into when using the SUM function.

Error 1: #VALUE! Error

Cause: Mixing data types like text and numbers in the sum range.

Solution: Ensure all cells in the range contain numeric values.

Error 2: Unexpected Results with Dates

Cause: Summing date-formatted cells directly.

Solution: Convert dates to numbers using DATEVALUE before summing.

For a more advanced topic, see how this function can be used to create a data model in Excel!

What Is The SUM Function Useful For?

The SUM function is a fundamental tool to Excel, and it can be used for plenty of work.

1. Data Validation: Ensures data integrity by checking the sum of a range against expected totals.
2. Basic Statistical Analysis: Quickly calculates total values, an essential step in many statistical methods.
3. Budget Tracking: Adds up expenses or income, helping in personal or business budget management.

You can also use SUM alongside the Trim Function for data analysis!

Similar Useful Functions

Other functions similar to the excel value function that are easy to use include:

  • COUNT: Counts the number of cells in a range rather than adding them. Check out our guide to the Count Function here!
  • AUTOSUM: AutoSum is a button, not a function, but can generate your SUM formula for you!
  • SUMIF: Adds cells based on a specified conditions

Taking advantage of these functions together will help build your Excel skills overall, but especially with comparing data in your worksheets!


The SUM function in Excel is a fundamental tool for anyone working with data.

It’s simple enough for beginners but can be combined with other functions for more complex tasks.

Understanding SUM is key to efficient data management and analysis in Excel.

Where to go next? Read on with this article to the Find Function!



About Ben Richardson

Ben is a director of Acuity Training which he has been running for over 10 years.

He is a Natural Sciences graduate from the University of Cambridge and a qualified accountant with the ICAEW.

He previously worked as a venture capitalist and banker and so had extensive experience with Excel from building financial models before moving to learn SQL, Microsoft Power BI and other technologies more recently.