Microsoft excel is one of the unavoidable tools for spreadsheet work. Though it offers large number features, many users do not even try simple functions. Summing or adding numbers is one such feature and people generally open calculator app for doing such mathematical operations. Actually, you can do this within Excel spreadsheet and we will explain various methods of getting total sum of numbers in Excel in this article. Note that addition of dates is different than adding numbers, you can check our earlier article on how to add and subtract dates in Excel.
Adding Numbers in Excel
Whether you have numbers in rows or columns, it is easy to find the sum in Excel. It is also possible to add numbers scattered randomly on a single sheet, across different sheets in a same workbook and across different workbooks or files.
1. Find Sum in Excel Status Bar
First and foremost, easy option is to find the sum of numbers showing in Excel’s status bar.
- Simply select all the numbers you want to find the total. You can select a range containing numbers and text content, Excel will ignore text and add only the available numbers.
- Excel will automatically show the sum in the status bar beside the workbook views changing options.
- You can also find the average and count of the selected numbers along with the sum.
- The latest Excel version in Windows also supports copying the sum to clipboard when you click on it.
Note: If you do not see the “Sum” option, right click anywhere on the status bar to open “Customize Status Bar” context menu. This will open the huge list of items, scroll down and enable “Sum” option from the list. You can also enable other useful functions like maximum, minimum, workbook statistics, etc.
2. Typing Sum Function in a Cell
Next option is to manually type sum function and find the total.
- For this, click on the cell where you want to show the total.
- Type the function
=sum
and Excel will show you the list of functions related to summing. - Double click to select “Sum” function from the list or continue typing open bracket symbol ( in the cell.
- Now you will see Excel showing the syntax of sum function to select the numbers for adding.
- Either use your mouse or keyboard arrow keys to select as many as numbers you want to add. You can select the numbers one by one and press enter or select the range.
- After selecting the numbers or range, close the function with ) and press enter to get the sum.
In the above examples, we have added the numbers in cell D2 and D3 and shown the total in D4. You can also directly type the function with the corresponding cells or range like below:
=sum(D2,D3)
=sum(D2:D3)
Make sure to separate individual numbers with comma and use colon when using range. If you get stuck, use the help menu and get the syntax for sum function to get the details within Excel.
3. Type Sum Function in Formula Box
Excel by default shows a formula box between the ribbon and cell content. If you do not see it, go to “View” menu and enable “Formula Bar” check box under “Show” group. Now, you can type the function inside the formula bar similar to typing the cell. First, select the cell where you want to show the total and start typing your sum function in the formula bar. You can type either individual cells or range as explained above. When typing in the formula bar also you will see the help details similar to typing sum function in the cell.
4. Use Summation from Home Menu
Next option is getting the total using the summation symbol available in Excel ribbon.
- Click on any empty cell where you want to show the sum. It could be a cell below multiple numbers in a column or beside multiple numbers in a row.
- Click on the “Sum” icon showing under “Home” menu under “Editing” group.
- You will instantly get the sum all numbers in that row or column.
The beauty with this option is that Excel will automatically select the numbers in a row or column and show the sum and you do not need to select the numbers manually.
5. Get Total in Excel with Keyboard Shortcuts
The sum icon in Excel ribbon toolbar also has a keyboard shortcut that you can use to quickly get the sum. Simply press Alt and + keys to get the sum of numbers in a row or column. As mentioned, Excel will automatically select all the numbers in that row or column and show the total. If you enter the shortcut in middle of multiple numbers where Excel can’t determine a row or column, it will show you the automatically selected range. You can simply press enter to get the total of automatically selected numbers or change the range to get that sum.
6. Add Number Ranges with Formula Builder
Excel also offers a Formula Builder which you can use for creating custom formula for adding numbers. For example, you can find the sum of range of numbers in a row and in a column together using Formula Builder. On Mac version, you can open Formula Builder from the “View” menu and it will open as a sidebar. On Windows version, you can click on the fx icon showing left to the formula bar to open Formula Builder and the tool will open as a pop-up window. However, the functionality remains same in both Windows and Mac versions.
- When Formula Builder opens, use the search box to find the “SUM” function. If you previously used sum function, you could find it from the “Most Recently Used” list of functions.
- Bottom portion of the tool will show you the syntax and other help material on how to use the selected SUM function. You can refer this tutorial when you are in doubt.
- Click on the “Insert Function” button to insert sum function in the selected cell.
- Excel will automatically add the total from the row or column based on the cell you selected. You can see this in the selected cell as well as in the “Number1” box of the Formula Builder.
- The advantage of Formula Builder is that you can add additional range to the existing total by inputting or selecting numbers in “Number2” box. For example, in the below example screenshot, we have used D2:D6 in Number1 and E2:F2 in Number2 and show the total in D7. After selecting all required numbers, Excel will also show the result in Formula Builder and press enter to see the result in the cell D7.
7. Adding Numbers from Different Sheets
All the above methods are dealing with the numbers available on a single sheet. Good part is that you can use any of the above method for adding numbers from multiple sheets. For example, we want to add the cells D2, D3 from sheet2 and E4, E5 from sheet3 and show the result in D4 of sheet2. In this case, the below sum function in D4 sheet2 can help to get the desired result:
=sum(D2:D3,Sheet!E4:E5)
You can simply use the sum function and select the cells by switching the sheets. Excel will automatically include the reference and show the total.
8. Adding Numbers Across Excel Workbooks
The last option we explain here is to add numbers available in two or multiple Excel workbooks. We take the same example in the above case with a difference that the cells E4 and E5 are in a new Excel workbook. In this case, you can use the following formula using sum function to get the total:
=SUM(D2:D3,[Book1]Sheet1!$E$4:$E$5)
Book1 and Sheet1 in the formula are from the new Excel workbook named Book1 containing the worksheet named Sheet1. Excel will automatically update the book and sheet name references when you change it on the file. When you close the new file, the formula will refer the complete path of the referenced file as shown in the below picture.
When you close all the files and reopen the one with the formula, Excel will ask you to update the external links and warn you that it could be unsafe. You can click “Update” to get the latest values of E4 and E5 from the other file or ignore the update to keep the old values.
Even if you moved or deleted the referenced file, Excel will continue to show the total sum as long you choose “Don’t Update” option. Otherwise, you will see an error and clicking on the cell where the total is displayed will open the browse window for your to choose the correct file location.
Leave a Reply
Your email is safe with us.