How to SUM in Excel (Table of Contents)
How to SUM in Excel
There are different ways to find the sum of the given numbers in excel. This can be particular cells, the range of cell values, some filtered values, etc. The easy methods to find the sum of cell values are given below.
- Cell’s sum in a single click
- Using AutoSum
- By manually entering the Sum function
- Applying Subtotal for filtered cells
Examples on How to SUM in Excel
- SUM (), AutoSum are the commonly used methods to find the sum in excel. SUM () arguments can be numbers, arrays, cell references, etc. It accepts up to 255 arguments. Let us look at a few examples.
- We will see how to SUM cell values in a Single Click. Below given are the names of some items purchased and its rate. We need to find the total or sum of the rates.
- The fast and easy method to get the sum of the given is, click on the letter of the column and check within the status bar to find the total of the selected column values.
- The sum of the entire column is shown in the status bar 4591.54.
- If you want to find the sum of the particular range of cells. You can find it just by selecting the range.
- In the same data if you want to find the sum of the rate from a particular range. Select the range of cells as below.
- Select the range from the second item to 7th once you check the status bar again you can see the sum of that particular selected items.
- If you want to find the sum of cells randomly that means not within a particular range.
- You can select the cells for which you want to find the sum by pressing the Ctrl key and click on the cells using the mouse without releasing the Ctrl key. Here the selection is shown below.
- Again check with the status bar to get the sum of selected cells. That is shown as 2028.79
Using AutoSUM in Excel
AutoSum is a built-in function within Excel. This will automatically calculate the sum of numbers and display in the formula applied cell. In the previous methods, we were not able to show the sum within a cell. This will display the value as per your wish.
- It gave some products purchased and the bill amount. Let’s find the sum of the given products.
- To avoid the range selection, select the immediate cell below the amount to find the sum.
- Go to Home menu then click on AutoSum as shown below.
- The range will be automatically selected and added the ‘=SUM’ function. If you want to change the range can select the range by picking the cells.
- After the selection press Enter Key on your keyboard. And the total will be displayed in the same cell.
Manually Entering the SUM Function
Another method is to use the ‘=Sum’ function manually. Apply the function and select the range of cells.
- Different fruits and their quantities are given below. Find the SUM of quantities.
- To get the SUM of the given Qty. Select the cell below the given Quantity and apply the formula ‘=Sum ()’
- This function will add the numbers in a range of cells. Within the function specify the range of cells for which you want to get the SUM.
- After selecting the cell range press Enter on the keyboard to get the result.
- The SUM of the given Quantity is 461.
Find SUM by Applying Subtotal for Filtered Cells
When you need to find the sum for a filtered cell that means from a long list you want to get the SUM of filtered items.
- Below given is the list of items purchased on Different Dates and their Quantity.
- We want to find the Total quantity of Fruits. First, we need to filter the column items to get the Quantity of the Fruits. Click on any cell then Go to the Data Tab then Click on Filter Icon.
- Once you Click on Filter icon you can see an arrow key appear near to each column headers.
- Click on the arrow near to the Items header. Uncheck the ‘Select All’ and check the values which you want to Filter. Here we will Filter Fruits. And you will get the columns as below.
- Click on the empty cell under column Quantity. Select AutoSum from Home. The function ‘SUBTOTAL’ will appear and the cell range will be shown as the entire column.
- Press Enter on the keyboard, and you can see only the filtered cells summed up.
Things to Remember About How to SUM in Excel
- While using the formula instead of selecting the cell range you can manually type the cell range. This will help a large number of cells.
- From a large number of cells if you want to get a particular item’s sum filter then AutoSum method is preferred.
- A ‘#VALUE’ error will produce when trying to find the sum of text with length more than 255 characters.
- Alt + = is the keyboard shortcut for AutoSum.
This has been a guide to How to SUM in Excel. Here we discussed Examples on How to SUM in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
- Tutorials on Insert Comment in Excel
- LEN Formula in Excel with Examples
- Best Examples on SUMIF Formula in Excel
- Insert Page Break | Excel Tutorials
All in One Excel VBA Bundle (120+ Courses, 30+ Projects)
4.8 (1,920 ratings)
120+ Online Courses
Excel Advanced course
Excel Data Analysis Course
Excel for HR Training