How to Use SUM in Excel

Greetings, fellow Excel users! Today’s topic is one of the most basic yet essential formulas in Excel – the SUM function. SUM is used to add numbers, and it’s a crucial tool for anyone working with data in Excel. Whether you’re a newbie or an experienced user, understanding how to use SUM is a must. In this blog post, we will go through the steps of how to use SUM in Excel, including its syntax, arguments, and some helpful tips. So let’s get started!

Syntax of SUM Formula

Before we dive into the step-by-step guide, let’s take a look at the basic syntax of the SUM function in Excel:

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

The function takes one or more arguments, which can be numbers, cell references, or ranges. Make sure to separate each argument with a comma within the parentheses. Also, note that the function name and all arguments are capitalized, but the cell references or ranges in the arguments are not.

Using SUM Formula for Adding Numbers

The most straightforward way to use SUM in Excel is to add up a list of numbers. Here’s how:

Step 1:

Open Excel and enter the numbers you want to add up into adjacent cells. For example, enter 5, 6, and 7 in cells A1, A2, and A3.

Step 2:

Select the cell where you want to display the total result. For this example, select cell A4.

Step 3:

Type in the function =SUM(A1:A3) into cell A4, and hit the Enter key. The formula will automatically calculate the total sum of numbers in cells A1 to A3, which is 18.

Using SUM Formula for Adding Cell References and Ranges

You can also use the SUM function to add up values from cell references or ranges. Here’s how:

Step 1:

Enter the values in separate cells or ranges.

Step 2:

Select the cell where you want to display the total result.

Step 3:

Type the formula with the cell references or ranges. Use a comma between each cell reference or range. For Example, =SUM(B2:B5,D2:D5).

Using SUM Formula with AutoSum

Excel provides a useful feature called AutoSum that automatically generates the SUM formula based on the data in your worksheet. Here’s how to use AutoSum for adding up a list of numbers:

Step 1:

Select a cell below the column of numbers to be summed.

Step 2:

Click on the AutoSum button in the Editing group on the Home tab or press Alt + =. Excel will automatically guess where the values are that you want to sum.

Step 3:

Hit the Enter key, which will create the formula. The sum of your selected data will now show up in the cell beneath your column of cells.

There you have it, a step-by-step guide on how to use the SUM function in Excel. It’s a powerful tool that can save you time and help you get accurate results efficiently. Remember, you can use SUM with cell references, ranges, or individual numbers, and you can even use AutoSum for quick calculation. Use this formula in your Excel sheets and tasks to simplify your work and makes it more efficient.

Using SUMIF Formula in Excel

Another useful tool in Excel is the SUMIF formula, which allows you to sum up values based on a set of criteria. Here’s how:

Step 1:

Enter the data into adjacent columns. For example, enter names in column A and exam scores in column B.

Step 2:

Select the cell where you want to display the total result.

Step 3:

Type the formula with the criteria. Here’s an example with specific criteria to add scores above 80.=SUMIF(B2:B5,">80"). This formula will sum up all the scores in cells B2 to B5 if they’re above 80.

Using SUMPRODUCT Function in Excel

SUMPRODUCT function is another way to find the sum of products of arrays. Here’s how:

Step 1:

Enter the data into adjacent columns. For example, enter prices in column A and quantities in column B.

Step 2:

Select the cell where you want to display the total result.

Step 3:

Type the formula with the criteria. Here’s an example with specific criteria to find the total product cost =SUMPRODUCT(A2:A5, B2:B5). This formula will calculate the total product cost by multiplying prices and quantities of each product and then adding the result.

AutoSum Shortcuts in Excel

AutoSum offers a variety of shortcuts to reduce the number of clicks it takes to perform a SUM function. Here are some of the AutoSum shortcuts:

Alt+=:

Pressing Alt and equal to keys at the same time will instantly add the SUM formula to the selected cells.

Ctrl+Shift+T:

This shortcut will add borders to the selected cells and auto SUM will be applied vertically.

Ctrl+Shift+R:

This shortcut will add borders to the selected cells and auto SUM will be applied horizontally.

Using the SUM Function in a Formula

The SUM function can be used as part of a larger formula. Here’s how:

Step 1:

Enter the formula including the SUM function along with the cell references and other formulas.

Step 2:

Use the parentheses to group the sum and other formulas.

Step 3:

Press Enter, and the formula will automatically calculate.

Wrap Up

Excel is an excellent tool for manipulating data, and the SUM function is a fundamental formula that every user should know. Whether you’re adding up values, using SUMIF to sum up values based on criteria, using SUMPRODUCT to find the sum of products, or inserting SUM into more complex formulas, the SUM function can help you get your work done faster and more accurately.

FAQ

Here are some common questions and answers related to using the SUM function in Excel.

Q: Can I add more than two ranges or cell references together using SUM?

A: Yes, you can add as many ranges or cell references as needed, just separate them with commas within the parentheses. For example, =SUM(A1:A5, B1:B5, C1:C5) will sum values from three different ranges.

Q: Can I use SUM with non-numerical values, such as text or dates?

A: No, the SUM function only works with numerical values. If you try to sum non-numerical values, Excel will return a #VALUE! error.

Q: Can I use SUM in Excel tables?

A: Yes, you can use SUM in Excel tables just like in any other worksheet. To add up a column of values in a table, type the SUM formula into the cell below the column of numbers, and use the column reference instead of cell reference. For example, =SUM(Table1[Column1]).

Q: How can I view the formula used in a cell?

A: To see the formula used in a cell, select the cell with the formula you want to view. Look at the formula bar located above the cells. The formula used in the selected cell is displayed in the formula bar.

Q: Can I use SUM with conditional formatting?

A: Yes, conditional formatting can be used with SUM to highlight specific values based on conditions. For example, you can use conditional formatting to highlight values that exceed a certain number. Here’s how you can apply conditional formatting with SUM:

1. Select the cells or range you want to apply conditional formatting to.
2. Click on the Conditional Formatting button in the Home tab, and select New Rule.
3. Choose “Use a formula to determine which cells to format”, and enter the formula with SUM function as a condition.