Table of Contents
SUM
Use the SUM function in Excel to sum a range of cells an entire column or non-contiguous cells. To create awesome SUM formulas combine the SUM function with other Excel functions.
Sum a Range
Most of the time you’ll use the SUM function in Excel to sum a range of cells.
Note: simply type =SUM(A1:A8) to enter this formula. When you arrive at: =SUM( instead of typing A1:A8 simply select the range A1:A8.
Sum an Entire Column
You can also use the SUM function in Excel to sum an entire column.
Note: you can also use the SUM function in Excel to sum an entire row. For example =SUM(5:5) sums all values in the 5th row.
Sum Non-contiguous Cells
You can also use the SUM function in Excel to sum non-contiguous cells. Non-contiguous means not next to each other.
Note: =A3+A5+A8 produces the exact same result!
AutoSum
Use AutoSum or press ALT + = to quickly sum a column or row of numbers.
1. First select the cell below the column of numbers (or next to the row of numbers) you want to sum.
2. On the Home tab in the Editing group click AutoSum (or press ALT + =).
3. Press Enter.
You can also use AutoSum to quickly add a total row and a total column.
4. For example select the range B2:F5 below.
5. Press ALT + =
Excel inserts 8 SUM functions! An easy but impressive trick.
Conditional Sum
The SUMIF function in Excel returns a conditional sum.
1. The SUMIF function below sums values in the range B1:B5 if the corresponding cells in the range A1:A5 contain exactly green.
2. The SUMIF function below sums today’s sales.
Note: today is August 3rd 2018. Visit our page about the SUMIF function for more information and examples.
Sum Every Nth Row
The SUM formula below uses SUM MOD and ROW to sum every nth row. Change the 3 to 4 to sum every 4th row to 5 to sum every 5th row etc.
Note: the formula bar indicates that this is an array formula by enclosing it in curly braces {}. Do not type these yourself. To enter an array formula finish by pressing CTRL + SHIFT + ENTER. In Excel 365 or Excel 2021 finish by simply pressing Enter. You won’t see curly braces.
Sum Largest Numbers
The SUM formula below uses SUM and LARGE to sum the largest numbers in a range. Change {1234} to {12345} to sum the 5 largest numbers.
Note: =LARGE(A1:A112) simply returns the second largest number.
Sum a Range with Errors
The SUM formula below uses SUM and IFERROR to sum a range with errors. You can also use the AGGREGATE function in Excel to sum a range with errors.
Note: the SUM function in Excel automatically ignores text values.
