Technology

Detailed, Simple and Fast Guide to Calculating Sums in Excel

The sum formula in Excel is one of the most basic and important formulas in Excel. The formula allows you to calculate the sum of numbers within a range of cells, columns, rows, or even the entire sheet. In this article, we will explore different ways to use the sum function in Excel.

Administrator

27 December, 2023

128

Excel is a popular spreadsheet software used for data processing and analysis. One of the most important features of Excel is its ability to perform mathematical operations, including summation. Let’s explore the different formulas for calculating sums in Excel in the following content.

Formula for calculating sum in Excel by selecting adjacent cells

To quickly calculate the sum of a column in an Excel table, the user can select the column to be summed, then use the mouse to highlight the column containing the values, and the result will be displayed at the bottom right corner of the Excel table.

Formula for calculating sum in Excel using the Alt + shortcut

Step 1: Highlight the range of values to be summed.

Step 2: Press the Alt + shortcut to get the sum of the values in the row below.

Formula for calculating sum in Excel using the SUM function

The SUM function is the most commonly used function for calculating sums in Excel. The formula for calculating sums in Excel using the SUM function is as follows: =SUM(selected cells).

Step 1: Open the Excel file > Select the cell in the table where you want to display the sum value of the selected cells.

Step 2: Enter the SUM formula: =SUM(selected cells) > Then press Enter to get the desired sum value.

Quick tip:: When the data range you want to sum is large and evenly located in the same column address, you can manually enter it as follows: =SUM(C2:C500) > Then press Enter to get the sum value from cell C2 to C500.

Formula for calculating sum in Excel using Autosum on the toolbar

AutoSum is a feature of Excel that allows you to calculate sums of numbers within a range with just one click.

Calculate column sums using AutoSum

Step 1: Select the range to be summed and the row immediately below the values.

Step 2: In the toolbar, under the Editing section, select AutoSum > The total sum will appear below.

Calculate row sums using AutoSum

Similar to calculating column sums using AutoSum.

Step 1: Select the range to be summed and the column immediately to the right of the values.

Step 2: In the toolbar, under the Editing section, select AutoSum > The sum result will appear in the rightmost column to the right.

Calculate combined row and column sums using AutoSum

Select the range of values to be summed and the sum result of both the row and column will be displayed when using the AutoSum function.

Formula for calculating sum in Excel using Subtotal

Subtotal in Excel is used to calculate sums of numbers within a range, including hidden values. The advantages of using Subtotal in Excel are: the ability to sum numbers in hidden columns/rows; the ability to sum numbers based on conditions; the ability to sum numbers in a range in multiple ways.

Step 1: Open the Excel file to be calculated > Select the header row.

Step 2: In the toolbar, under the Editing section, select Sort & Filter > Select Filter.

Step 3: Select the arrow in the upper right corner of the header, the numerical values will be displayed.

Step 4: Select the values to be summed > Press OK.

Step 5: To calculate the sum, highlight the values > Press the shortcut key Alt + to get the desired sum result.

Common errors when calculating sums in Excel and how to fix them

During the use of Excel, you may encounter some errors when calculating sums. Here are some common errors and how to fix them when calculating sums in Excel:

Error #VALUE!: This error occurs when you enter an inappropriate value into a cell. For example, you enter the word “abc” into a cell formatted as a number.
Error #DIV/O!: This error occurs when you try to divide a number by zero. For example, you use the SUM function to calculate the sum of numbers in a range that does not contain any numbers.
Error #NUM!: This error occurs when you use an incorrect calculation formula. For example, you use the SUM function to calculate the sum of numbers in a range that contains empty cells.
Error #NAME?: This error occurs when you use a non-existent function name. For example, you enter SUMM instead of SUM.
Error #REF!: This error occurs when you refer to a non-existent cell. For example, you delete a cell that you have referenced in a formula.

How to fix errors when calculating sums in Excel:

Check the cell format: Make sure that the cells you are using in your formula are formatted correctly.
Check the cell values: Check that the values in the cells you want to calculate in the formula are correct.
Check the formula: Use and enter the formula correctly with no spelling errors.

Conclusion

With the detailed guide on formulas for calculating sums in Excel, readers will be able to handle data effectively. Don’t forget to follow the latest interesting articles from FPT Shop for more useful knowledge.