The tutorial explains how to do sum in Excel by using the AutoSum feature, and how to make your own SUM formula to total a column, row or selected range. You will also learn how to sum only visible cells, calculate running total, sum across sheets, and find out why your Excel Sum formula is not working.
If you want a quick sum of certain cells in Excel, you can simply select those cells, and look at the status bar at the bottom right corner of your Excel window:
For something more permanent, use the Excel SUM function. It is very simple and straightforward, so even if you are a beginner in Excel, you will hardly have any difficulty in understanding the following examples.
- How to sum in Excel using simple arithmetic calculations
- Excel SUM function
- AutoSum in Excel
- How to sum a column in Excel
- How to sum rows in Excel
- Excel Total Row to sum data in a table
- How to sum only filtered (visible) cells
- How to calculate running total (cumulative sum)
- How to sum across sheets
- Excel conditional sum
- Excel Sum not working - fixes and solutions
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:
SUM(number1, [number2] ,…)
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.
Tip. A faster way to do AutoSum in Excel is to use the Sum shortcut Alt + =. Just hold the Alt key, press the Equal Sign key, and then hit Enter to complete an automatically inserted Sum formula.
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.
Sum column except header or excluding a few first rows
Usually, supplying a column reference to the Excel Sum formula totals the entire column ignoring the header, as demonstrated in the above screenshot. But in some cases, the header of the column you want to total can actually have a number in it. Or, you may want to exclude the first few rows with numbers that are not relevant to the data you want to sum.
Regrettably, Microsoft Excel does not accept a mixed SUM formula with an explicit lower bound but without an upper bound like =SUM(B2:B), which works fine in Google Sheets. To exclude the first few rows from summation, you can use one of the following workarounds.
- Sum the entire column and then subtract the cells you don't want to include in the total (cells B1 to B3 in this example):
=SUM(B:B)-SUM(B1:B3)
- Remembering the worksheet size limits, you can specify the upper bound of your Excel SUM formula based on the maximum number of rows in your Excel version.
For example, to sum column B without the header (i.e. excluding cell B1), you can use the following formulas:
- In Excel 2007, Excel 2010, Excel 2013, and Excel 2016:
=SUM(B2:B1048576) - In Excel 2003 and lower:
=SUM(B2:B655366)
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)
How to sum multiple rows in Excel
To add values in each row individually, just drag down your Sum formula. The key point is to use relative (without $) or mixed cell references (where the $ sign fixes only the columns). For example:
=SUM($B2:$D2)
To total the values in a range containing several rows, simply specify the desired range in the Sum formula. For example:
=SUM(B2:D6) - sums values in rows 2 to 6.
=SUM(B2:D3, B5:D6) - sums values in rows 2, 3, 5 and 6.
How to sum a whole row
To sum the entire row with an indefinite number of columns, supply a whole-row reference to your Excel Sum formula, e.g.:
=SUM(2:2)
Please remember that you shouldn't enter that 'Sum of a row' formula in any cell of the same row to avoid creating a circular reference because this would result in a wrong calculation, if any:
To sum rows excluding a certain column(s), total the entire row and then subtract irrelevant columns. For example, to sum row 2 except the first 2 columns, use the following formula:
=SUM(2:2)-SUM(A2:B2)
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 add a total row in Excel tables
Once your data is arranged in a table, you can insert a total row in this way:
- Click anywhere in the table to display the Table Tools with the Design tab.
- On the Design tab, in the Table Style Options group, select the Total Row box:
Another way to add a total row in Excel is to right click any cell within the table, and then click Table > Totals Row.
How to total data in your table
When the total row appears at the end of the table, Excel does its best to determine how you would like to calculate data in the table.
In my sample table, the values in column D (rightmost column) are added automatically and the sum is displayed in the Total Row:
To total values in other columns, simply select a corresponding cell in the total row, click the drop-down list arrow, and select Sum:
If you want to perform some other calculation, select the corresponding function from the drop-down list such as Average, Count, Max, Min, etc.
If the total row automatically displays a total for a column that doesn't need one, open the dropdown list for that column and select None.
Note. When using the Excel Total Row feature to sum a column, Excel totals values only in visible rows by inserting the SUBTOTAL function with the first argument set to 109. You will find the detailed explanation of this function in the next section.
If you want to sum data both in visible and invisible rows, do not add the total row, and use a normal SUM function instead:
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:
SUBTOTAL(function_num, ref1, [ref2],…)
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:
Tip. You can have Excel's AutoSum feature to insert the Subtotal formula for you automatically. Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.
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
Note. The Conditional Sum functions are available in Excel versions beginning with Excel 2003 (more precisely, SUMIF was introduced in Excel 2003, while SUMIFS only in Excel 2007). If someone still uses an earlier Excel version, you'd need to make an array SUM formula as demonstrated in Using Excel SUM in array formulas to conditionally sum cells.
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.
1. #Name error appears instead of the expected result
It's the easiest error to fix. In 99 out of 100 cases, the #Name error indicates that the SUM function is misspelled.
2. Some numbers are not added
Another common reason for a Sum formula (or Excel AutoSum) not working are numbers formatted as text values. At first sight, they look like normal numbers, but Microsoft Excel perceives them as text strings and leaves them out of calculations.
One of the visual indicators of text-numbers are the default left alignment and green triangles in top-left corner of the cells, like in the right-hand sheet in the below screenshot:
To fix this, select all problematic cells, click the warning sign, and then click Convert to Number.
If against all expectations that does not work, try other solutions described in: How to fix numbers formatted as text.
3. Excel SUM function returns 0
Apart from numbers formatted as text, a circular reference is a common source of problem in Sum formulas, especially when you are trying to total a column in Excel. So, if your numbers are formatted as numbers, but your Excel Sum formula still returns zero, trace and fix the circular references in your sheet (Formula tab > Error Checking > Circular Reference). For the detailed instructions, please see How to find a circular reference in Excel.
4. Excel Sum formula returns a higher number than expected
If against all expectations your Sum formula returns a bigger number than it should, remember that the SUM function in Excel adds both visible and invisible (hidden) cells. In this case, use the Subtotal function instead, as demonstrated in How to sum only visible cells in Excel.
5. Excel SUM formula not updating
When a SUM formula in Excel continues to show the old total even after you've updated the values in the dependent cells, most likely Calculation Mode is set to Manual. To fix this, go to the Formulas tab, click the dropdown arrow next to Calculate Options, and click Automatic.
Well, these are the most common reasons for SUM not working in Excel. If none of the above is your case, check out other possible reasons and solutions: Excel formulas not working, not updating, not calculating.
This is how you use a SUM function in Excel. If you want to have a closer look at the formula examples discussed in this tutorial, you are welcome to download a sample Excel SUM workbook. I thank you for reading and hope to see you on our blog next week.
You may also be interested in
- How to use SUMIF in Excel - formula examples to conditionally sum cells
- How to use Excel SUMIFS and SUMIF with multiple criteria - formula examples
- How to add and subtract time in Excel
- How to add and subtract dates, days, weeks, months and years
- How to get mean, median and mode in Excel
- Excel MEDIAN formula examples
FAQs
How do you sum in Excel excluding hidden rows? ›
For example you want to sum only visible cells only, please select the cell you will place the summing result at, type the formula =SUMVISIBLE(C3:C12) (C3:C13 is the range where you will sum only visible cells) into it, and press the Enter key. And then the summing result is calculated with ignoring all hidden cells.
How do you add a conditional sum in Excel? ›If you want, you can apply the criteria to one range and sum the corresponding values in a different range. For example, the formula =SUMIF(B2:B5, "John", C2:C5) sums only the values in the range C2:C5, where the corresponding cells in the range B2:B5 equal "John."
How do I count only visible cells in Excel? ›If you're using Excel 2007 or later, you can also use the COUNTA function to count filtered rows. This function counts all non-blank cells in a range. So if you have a column of data that has some blank cells, COUNTA will only count the non-blank cells.
What Excel functions is used to get the total value of numbers in rows or columns? ›If you need to sum a column or row of numbers, let Excel do the math for you. Select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and you're done. When you click AutoSum, Excel automatically enters a formula (that uses the SUM function) to sum the numbers.
How do I SUM only visible cells not hidden? ›Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.
How do you sum using conditions? ›To sum with multiple criteria, either use the SUMIFS function (adds up cells that meet all the conditions) or build a SUMIF formula with multiple OR criteria (sums cells that meet any of the conditions).
How do you sum by two criteria in Excel? ›=SUMIFS(D2:D11,
In other words, you want the formula to sum numbers in that column if they meet the conditions. That cell range is the first argument in this formula—the first piece of data that the function requires as input.
Count ignore hidden cells and rows with excel functions
Select a blank cell you will place the counting result into, type the formula =SUBTOTAL(102,C2:C22) (C2:C22 is the range where you want to count ignoring manually hidden cells and rows) into it, and press the Enter key.
Tips: To sum the visible cells based on multiple criteria, you just need to add the criteria like this: =SUMPRODUCT(SUBTOTAL(3,OFFSET(reference,ROW(reference)-MIN(ROW(reference)),,1)),(criteria_range1=criteria1)*(criteria_range2=criteria2)*(criteria_range3=criteria3)*(sum_range)).
How do you count filtered cells? ›To count filtered rows in Excel, you can use the SUBTOTAL function that allows you to select its count feature in the first argument and the range from which you want to count filtered rows. After using this formula, when you apply a filter on that range and filter cells, it will only count the visible rows.
What is the formula for sum in Excel? ›
=SUM(A2:A10) Adds the values in cells A2:10. =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.
What is the formula of subtotal in Excel? ›| Data | ||
|---|---|---|
| Formula | Description | Result |
| =SUBTOTAL(9,A2:A5) | The sum of the subtotal of the cells A2:A5, using 9 as the first argument. | 303 |
| =SUBTOTAL(1,A2:A5) | The average of the subtotal of the cells A2:A5, using 1 as the first argument. | 75.75 |
| Notes |
- Select a cell in a table.
- Select Design > Total Row.
- The Total row is added to the bottom of the table. ...
- From the total row drop-down, you can select a function, like Average, Count, Count Numbers, Max, Min, Sum, StdDev, Var, and more.
The same goes for hidden cells; since the SUM function takes a consecutive range (unless manually inputted with separate cells), hidden cells will also be included by the SUM function in counting the total.
How do you sum data based on multiple criteria? ›You use SUMIFS in Excel to find a conditional sum of values based on multiple criteria. The SUMIFS function was introduced in Excel 2007 and is available in all subsequent versions of Excel 2010, 2013, 2016, 2019, 2021, and Excel 365.
How do I sum multiple columns in Excel with criteria? ›SUMIFS + SUMIFS to sum multiple columns
To sum cells that match multiple criteria, you normally use the SUMIFS function.
Sum if cell contains text
If you are looking for an Excel formula to find cells containing specific text and sum the corresponding values in another column, use the SUMIF function. Where A2:A10 are the text values to check and B2:B10 are the numbers to sum. To sum with multiple criteria, use the SUMIFS function.
Things to remember about the SUM Function
The easiest way to apply the function is to simply select a cell next to the numbers that we wish to add and click on AutoSum on the Home tab. We can then press the Enter key and the SUM formula is automatically inserted.
If you clicked IF, the Function arguments dialog box displays the arguments for the IF function. To nest another function, you can enter it into the argument box. For example, you could enter SUM(G2:G5) in the Value_if_true box of the IF function. Enter any additional arguments that are needed to complete your formula.
Can you use two conditions in a Sumif? ›If you need to create a sum based on more than one criteria, you can use SUMIFS. It accomplishes the same task as SUMIF but allows you to set anywhere from one to 255 conditions. I have created a workbook with an example of using SUMIFS.
Can I use Sumifs for rows and columns? ›
If you only need to sum matching columns (not rows) you can use a formula like this. To sum matching rows only, you can use the SUMIFS function.
How do I exclude hidden in subtotal in Excel? ›As you remember, Subtotal formulas with function_num 101 to 111 ignore all hidden cells - filtered out and hidden manually. So, when you use Excel's Hide feature to remove irrelevant data from view, use function number 101-111 to exclude values in hidden rows from subtotals.
How do I sum only visible cells in sheets? ›If we attempt to use the SUM() function to sum the points column of the filtered rows, it will actually return the sum of all of the original values: What is this? Instead, we can use the SUBTOTAL() function: This function takes the sum of only the visible rows.
What is the aggregate function in Excel? ›The AGGREGATE function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using option 1, such as AGGREGATE(1, 1, ref1), hiding a column does not affect the aggregate sum value.
How do you exclude cells from sum? ›If cells you need to exclude from the summing locating in discontinuous cells, for example, you need to exclude values in cell A3 and A6 while summing range A2:A7, please apply this formula: =SUM(A2:A7)-A3-A6.
What is the Sumifs function in Excel? ›The SUMIFS function, one of the math and trig functions, adds all of its arguments that meet multiple criteria. For example, you would use SUMIFS to sum the number of retailers in the country who (1) reside in a single zip code and (2) whose profits exceed a specific dollar value. Play.