Excel SUM formula to total a column, rows or only visible cells

How to sum in Excel using a simple arithmetic calculation

If you need a quick total of several cells, you can use Microsoft Excel as a mini calculator. Just utilize the plus sign operator (+) like in a normal arithmetic operation of addition. For example:

=1+2+3

or

=A1+C1+D1

However, if you need to sum a few dozen or a few hundred rows, referencing each cell in a formula does not sound like a good idea. In this case, you can use the Excel SUM function specially designed to add a specified set of numbers.

How to use SUM function in Excel

Excel SUM is a math and trig function that adds values. The syntax of the SUM function is as follows:

The first argument is required, other numbers are optional, and you can supply up to 255 numbers in a single formula.

In your Excel SUM formula, each argument can be a positive or negative numeric value, range, or cell reference. For example:

=SUM(A1:A100)

=SUM(A1, A2, A5)

=SUM(1,5,-2)

The Excel SUM function is useful when you need to add up values from different ranges, or combine numeric values, cell references and ranges. For example:

=SUM(A2:A4, A8:A9)

=SUM(A2:A6, A9, 10)

The below screenshot shows these and a few more SUM formula examples:

In real-life worksheets, the Excel SUM function is often included in bigger formulas as part of more complex calculations.

For example, you can embed SUM in the value_if_true argument of the IF function to add numbers in columns B, C and D if all three cells in the same row contain values, and show a warning message if any of the cells is blank:

=IF(AND($B2<"", $C2<>"", $D2<>""), SUM($B2:$D2), "Value missing")

And here's another example of using an advanced SUM formula in Excel: VLOOKUP and SUM formula to total all matching values.

How to AutoSum in Excel

If you need to sum one range of numbers, whether a column, row or several adjacent columns or rows, you can let Microsoft Excel write an appropriate SUM formula for you.

Simply select a cell next to the numbers you want to add, click AutoSum on the Home tab, in the Editing group, press the Enter key, and you will have a Sum formula inserted automatically:

As you can see in the following screenshot, Excel's AutoSum feature not only enters a Sum formula, but also selects the most likely range of cells that you'd want to total. Nine times out of ten, Excel gets the range right. If not, you can manually correct the range by simply dragging the cursor through the cells to sum, and then hit the Enter key.

Apart from calculating total, you can use AutoSum to automatically enter AVERAGE, COUNT, MAX, or MIN functions. For more information, please check out the Excel AutoSum tutorial.

How to sum a column in Excel

To sum numbers in a specific column, you can use either the Excel SUM function or AutoSum feature.

For example, to sum values in column B, say in cells B2 to B8, enter the following Excel SUM formula:

=SUM(B2:B8)

Total an entire column with indefinite number of rows

If a column you want to sum has a variable number of rows (i.e. new cells can be added and existing ones can be deleted at any time), you can sum the entire column by supplying a column reference, without specifying a lower or upper bound. For example:

=SUM(B:B)

Important note! In no case you should put your 'Sum of a column' formula in the column you want to total because this would create a circular cell reference (i.e. an endless recursive summation), and your Sum formula would return 0.

How to sum rows in Excel

Similarly to totaling a column, you can sum a row in Excel by using the SUM function, or have AutoSum to insert the formula for you.

For example, to add values in cells B2 to D2, use the following formula:

=SUM(B2:D2)

Use Excel Total Row to sum data in a table

If your data is organized in an Excel table, you can benefit from the special Total Row feature that can quickly sum the data in your table and display totals in the last row.

A big advantage of using Excel tables is that they auto-expand to include new rows, so any new data you input in a table will be included in your formulas automatically. If can learn about other benefits of Excel tables in this article: 10 most useful features of Excel tables.

To convert an ordinary range of cells into a table, select it and press Ctrl + T shortcut (or click Table on the Insert tab).

How to sum only filtered (visible) cells in Excel

Sometimes, for more effective date analysis, you may need to filter or hide some data in your worksheet. A usual Sum formula won't work in this case because the Excel SUM function adds all values in the specified range including the hidden (filtered out) rows.

If you want to sum only visible cells in a filtered list, the fastest way is to organize your data in an Excel table, and then turn on the Excel Total Row feature. As demonstrated in the previous example, selecting Sum in a table's total row inserts the SUBTOTAL function that ignores hidden cells.

Another way to sum filtered cells in Excel is to apply an AutoFilter to your data manually by clicking the Filter button on the Data tab. And then, write a Subtotal formula yourself.

The SUBTOTAL function has the following syntax:

Where:

• Function_num- a number from 1 to 11 or from 101 to 111 that specifies which function to use for the subtotal.

  You can find the full list of functions on support.office.com. For now, we are interested only in the SUM function, which is defined by numbers 9 and 109. Both numbers exclude filtered-out rows. The difference is that 9 includes cells hidden manually (i.e. right-click > Hide), while 109 excludes them.

  So, if you are looking to sum only visible cells, regardless of how exactly irrelevant rows were hidden, then use 109 in the first argument of your Subtotal formula.

• Ref1, Ref2, … - cells or ranges that you want to subtotal. The first Ref argument is required, others (up to 254) are optional.

In this example, let's sum visible cells in range B2:B14 by using the following formula:

=SUBTOTAL(109, B2:B14)

And now, let's filter only 'Banana' rows and make sure that our Subtotal formula sums only visible cells:

How to do a running total (cumulative sum) in Excel

To calculate a running total in Excel, you write a usual SUM formula with a clever use of absolute and relative cells references.

For example, to display the cumulative sum of numbers in column B, enter the following formula in C2, and then copy it down to other cells:

=SUM($B$2:B2)

The relative reference B2 will change automatically based on the relative position of the row in which the formula is copied:

You can find the detailed explanation of this basic Cumulative Sum formula and tips on how to improve it in this tutorial: How to calculate running total in Excel.

How to sum across sheets

If you have several worksheets with the same layout and the same data type, you can add the values in the same cell or in the same range of cells in different sheets with a single SUM formula.

A so-called 3-D reference is what does the trick:

=SUM(Jan:Apr!B6)

Or

=SUM(Jan:Apr!B2:B5)

The first formula adds values in cell B6, while the second formula sums the range B2:B5 in all worksheets located between the two boundary sheets that you specify (Jan and Apr in this example):

You can find more information about a 3-d reference and the detailed steps to create such formulas in this tutorial: How to create a 3-D reference to calculate multiple sheets.

Excel conditional sum

If your task requires adding only those cells that meet a certain condition or a few conditions, you can use the SUMIF or SUMIFS function, respectively.

For example, the following SUMIF formula adds only those amounts in column B that have "Completed" status in column C:

=SUMIF(C:C,"completed",B:B )

To calculate a conditional sum with multiple criteria, use the SUMIFS function. In the above example, to get the total of "Completed" orders with the amount over $200, use the following SUMIFS formula:

=SUMIFS(B:B,C:C,"completed",B:B, ">200" )

You can find the detailed explanation of the SUMIF and SUMIFS syntax and plenty more formula examples in these tutorials:

• SUMIF function in Excel: examples for numbers, dates, text, blanks and not blanks
• SUMIF in Excel - formula examples to conditionally sum cells
• How to use Excel SUMIFS and SUMIF with multiple criteria

Excel SUM not working - reasons and solutions

Are you trying to add a few values or total a column in your Excel sheet, but a simple SUM formula doesn't compute? Well, if the Excel SUM function is not working, it's most likely because of the following reasons.

Practice workbook for download

Excel SUM function - examples (.xlsx file)