How to calculate the sum in Excel using formulas or the function

How to calculate the sum in Excel using formulas or the function

Simple addition in a formula

The easiest way to sum cell values is to write them in a formula bar. To do this, click on the formula bar, enter the equals sign and the required cells separated by a plus sign - just like in elementary school math class.

=A1+B2

However, this method, for all its simplicity, has significant disadvantages. To add up a large range of cells, you will have to enter them all, which will take quite a lot of time and make the formula cumbersome. It will be difficult to edit it in the future.

The second problem is that if the value in any of the cells is not numeric but text, the whole formula will fail and generate the #VALUE! error, since this type of addition cannot skip text.

Example of an error when adding non-numeric values in Excel

You can avoid this result by using another method - the standard Excel function SUM, which allows you to sum cell values while eliminating the above problems.

The SUM function

Function syntax

=SUM(number1;[number2];…)

Arguments of the function

Number1 - Required. The first number to add. This can be a number, a cell reference, or a range of cells.

Number2 - Optional. The second number to add. Up to 255 numbers can be used within the function.

Example - Summarize cell A1 and B1:

=SUM(A1;B1)

To specify a summation range, specify its outermost cells separated by a colon - A1:B3. To summarize cells in the range A1:A20:

=SUM(А1:А20)

You can also insert not only cells but also regular numbers into the formula. Adding cell C1 and the number 5:

=SUM(С1;5)

All of these options can be used in one function. Adding cells in the range A1:A20, cell C1 and the number 5:

=SUM(A1:A20;С1;5)

Unlike the previous method, the SUM function simply skips cells containing non-numeric values. This avoids unnecessary errors in formulas.

AutoSum

If you need to quickly summarize data in a column or row, you can use Excel's integrated AutoSum tool. To do this, select the row or column whose cells you want to sum and click the Sum button in the Home tab, Edit group.

Autosum menu in Excel

You can also use the Alt + = hotkeys. Highlight the cell where the formula with the sum calculation should be. This cell must be in the same column or row as the list of values for Excel to understand what you want to sum. Press Alt + = and Excel will automatically find the closest matching range, put the formula with the SUM function on it.

This method is convenient to use, for example, to quickly calculate the total of a table column without formatting it.

Status bar

In addition, if you don't need to sum cells on the worksheet, but you need to quickly find out the result of summing several cells, you can select the necessary cells and view their sum in Excel's status bar.

Indicators in the Excel status bar

If the Sum indicator is not there, right-click and check the Sum box so that it is also displayed.

Customizing the sum in the status bars in Excel

Unfortunately, if text is encountered in the selected cells, the sum will not appear in the status bar.

Table sums

You can use "smart tables" to summarize columns. To do this, create a table with data, format it as a "smart table" - by pressing Ctrl+T or in the menu in the "Home" tab in the "Styles" group, select "Format as table". After that, select any cell in the table range.

In the tab bar you should see the item "Working with tables > Constructor". Go to it and in the "Table Style Options" group, check the "Summary Row" box. At the end of the table, a row with the summation totals will appear on the sheet.

Summing from different sheets

If you need to perform summing from different sheets, you also can use the SUM function. The only difference is that the reference to the cells on the other sheet will have an entry of the form "Sheet1!A1:A2". Then the addition formula on the first sheet referring to the second sheet will look like this:

=SUM(A1;4;List2!A1:A2)

I hope this article was helpful to you, and answered all your questions about summarizing in Excel. Have a nice day!

Please rate this article
(5 stars / 2 votes)