What is the most efficient way to access multiple cells in the same spreadsheet file?
Show
Home > Formulas > Sum Examples Excel Sum Function ExamplesExamples show how to sum in Microsoft Excel, with a simple SUM function, or formulas that sum based on one or more criteria. First, for a quick overview, watch the video: 7 Ways to Sum in Excel. Next, scroll down for more Sum function videos, written steps, and free Excel workbooks. Also, there is a Topic List at the bottom of the page, if you're looking for a specific How to Sum topic. Author: Debra Dalgleish Sum a range of cells -- SUM FunctionThe quickest and easiest way to sum a range of cells is to use the Excel AutoSum button. It automatically enters an Excel SUM function in the selected cell. The SUM function totals one or more numbers in a range of cells. The first example, below, shows how to use the AutoSum feature
The SUM Function SetupInstead of using the AutoSum command to insert the SUM function, you can type the function manually. The SUM function setup (syntax) is: SUM(number1, [number2],...).
These arguments can be cell references, or can be typed into the formula. In the example above (=SUM(A1:A4)), there is one argument -- a reference to cells A1:A4. Fix numbers that don't add upSome Excel values look like numbers, but don't add up, because Excel thinks they are text. Sometimes, you can fix the problem with Paste Special. Watch this short video tutorial, to see the steps For the written steps, and other ways to fix the problem, go to the Numbers Don't Add Up page. Grand Total a range of cellsIn one quick step, you can calculate the row, column, and grand totals for a range of cell. Watch this short Excel Grand Totals video, to see how to do it. There are written instructions below the video. Quick Grand Total for a range of cells
Running TotalTo see a running total in each row of an Excel list, you can use the SUM function, with the starting row locked as an absolute reference. There are slightly different steps below,
Running Total in Worksheet ListFor a worksheet list (not a named Excel table), this video shows how to set up the running total formula, and lock the starting row. The written steps are below. For the video transcript, go to the Running Total Video page. Running Total - Worksheet ListFor a worksheet list (not a named Excel table), follow these steps to create a running total. In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.
How the Formula WorksThe formula uses an absolute reference to row 2 as the starting point -- C$2 -- and a relative reference to the ending point -- C2 This ensures that the starting point will not change when you copy the formula down to the rows below. Here is the formula in cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6
Running Total in Excel TableFor a named Excel table, we can't use the worksheet list formula in the previous section. First, I'll show you the problem with that formula, and then you'll see the formula that works in a named table. Problem with Running TotalAfter you enter the formula in cell D2 of the named Excel table, it automatically fills down, and the running total looks correct. But, as soon as you start a new row at the bottom of the table, the formula in the last row changes. Before the row was added, cell D6 had this formula:
As soon as the next entry was started in row 7, the formula in D6 automatically changed. Now it has an incorrect ending reference to C7, instead of C6:
As each new row is added, the formulas in the bottom rows keep changing, to show the latest row number. Running Total Formula for Named TableTo avoid that problem, we'll use a slightly different formula for a running total in a named Excel table. In the screen shot below, amounts are entered in column C, and a running total is calculated in column D.
How the Formula WorksThe formula uses an absolute reference to a heading cell as the starting point -- C$1
For the ending point, there is a structured table reference -- [@Amt]
This screen shot shows that when a new row is started, cell C6 continues to show its original formula, and the running total amounts are correct in each row Sum a range of cells -- OFFSETIf you insert a row directly above the SUM function in the previous example, the new row may not be included in the SUM. It may continue to sum cells A1:A4, and ignore A5. To ensure that new rows are included in the total, you can use the OFFSET function with the SUM function.
Sum cells that match criteria -- SUMIFHere are 3 ways to sum cells that match criteria, using the Excel SUMIF function: - Match criterion exactly - Match criterion in a string - Match criterion using operator Tip: For examples on how to sum based on multiple criteria, go to the Sum cells with complex multiple criteria section, below. SUMIF Function SetupThe SUMIF function setup (syntax) is: SUMIF(range, criteria, [sum_range])
These arguments can be cell references, or can be typed into the formula. Match criterion exactlyYou can calculate a total for rows that meet a specific criterion. In this example only the rows with Pen orders will be included in the total.
Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to: Match criterion in a stringYou can add cells that contain a criterion as part of the cell's contents. In this example all Pen, Gel Pen, and Pencil orders will be summed, because they contain the string "pen".
Note: Instead of typing the criterion in a formula, you can refer to a cell.
For example, the formula in step 9 above could be changed to: Criterion and operatorYou can use an operator with a criterion. In the examples below, see how to combine them in the SUMIF formula. Example 1 Example 2 Example 1 - Sum Rows Greater Than Set AmountIn this example only the rows where the number of sales reps is greater than or equal to ten will be included in the total.
Note: Instead of typing the criterion in a formula, you can refer to a cell. For example, the formula in step 9 above could be changed to: Example 2 - Rolling 12 Month TotalIn this example only the rows for the previous 11 months, and the current month, will be included in the total. This creates a Rolling Total. The dates are in column A, and the list must be sorted by date. The monthly amounts are in column B.
How It Works The formula checks the dates in column A, starting in row 2 (A$2), and down to the current row (A2) =SUMIF(A$2:A2 The DATE function calculates the date that is 11 months prior to date in current row DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)) The >= operator checks for dates that are greater than or equal to that date, ">=" & DATE(YEAR(A2),MONTH(A2)-11,DAY(A2)) For rows that meet the criterion, the formula sums the amounts in column B, starting in row 2 (B$2) down to the current row (B2) B$2:B2 Sum cells that match multiple criteria -- SUMIFSIn Excel 2007 and later versions, you can use the SUMIFS function to calculate a total for rows that meet two or more criteria. Watch this short video to see the steps. There are written instructions below the video. For the full video transcript, go to the Sum Amounts With 2 Criteria Video page. SUMIFS Function SetupThe SUMIFS function setup (syntax) is: SUMIFS(range, criteria, [sum_range])
These arguments can be cell references, or can be typed into the formula. Match multiple criteriaIn this example, the SUMIFS function will sum the amounts in column D, based on 2 criteria:
Those criteria are entered in cells F6 (Shipped) and G6 (10) Follow these steps to create the SUMIFS formula:
Formulas With Table ReferencesIf you create formulas with table references, and then try to copy those formulas to adjacent columns, you might run into problems. This video shows the problem, and two ways to prevent it. There are written steps below the video. Problems with Table ReferencesIn this sales summary, there is a SUMIFS formula in cell C5, which is showing the correct total for Bars sales in the East region. =SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4) However, if you point to the fill handle in cell C5, and drag to the right, the formula shows an incorrect total in cell D5. If you check the formula in cell D5, all of the table references have shifted one column to the right, because the formula was dragged one column to the right. =SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Product],D$4)
None of those criteria are found, so the result is zero. To prevent this problem of shifting table references, don't drag the fill handle to copy across. Instead, use one of the following methods:
Fill Right
Copy and Paste
SUMIFS With Multiple AND/OR CriteriaIn the list below, you could use a SUMIFS formula to total the rows where:
AND
The formula in cell G9 would be:
Formula with Multiple Criteria for AND / ORInstead of just one category though, we would like to calculate the total for two or more categories. In this example, we'll calculate the total where:
OR
The two categories are entered in cells G6:G7 on the worksheet. Array-Entered FormulaTo calculate the total, we'll wrap the SUMIFS formula with a SUM function, and enter the formula as an array.
Sum Cells Match Multiple Criteria -- SUMPRODUCTMatch multiple criteriaIn Excel 2003 and earlier, you can use the SUMPRODUCT function to calculate a total for rows that meet two or more criteria. If you're using Excel 2007 or later, you should use the SUMIFS function, as described in the previous section. In this example only the rows where the status is "Active" and the number of visits is greater than or equal to ten will be included in the total.
Sum Top 5 Numbers in ListUse the SUM function and LARGE functions together, to add the largest numbers in the list. Version 1 -- Few Top NumbersIf a few numbers are to be summed, e.g. top 3, you can type the numbers into the formula. For example: =SUM(LARGE(A1:A7,{1,2,3})) The result is 70+60+50 = 180 Note: The second 50 is not included in the result, even though it is tied for 3rd place. Version 2 -- Many Top NumbersIf many top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function. In the INDIRECT function, use row numbers that represent the numbers you want to include. In this example, rows 1:10 are used, so the top 10 numbers in the referenced range will be summed.
Version 3 -- Variable Top NumbersIf a variable number of top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function, as shown above, and refer to a cell that holds the variable..
Sum Amounts in a Date RangeTo sum amounts based on a date range, you can use the SUMIFS function in Excel 2007 or later versions. Watch this video to see the steps, and the written instructions are below the video. Sum Amounts in a Date RangeTo total the amounts in a specific date range, use the SUMIFS function (Excel 2007 and later) or the SUMIF function. There are two examples below: - Total with SUMIFS - Total with SUMIF In this example, a Start date and an End date are entered on the worksheet. Dates are in column A, and units sold are in column B. See more Date Range examples on the Sum or Count for a Date Range page. Use SUMIFS to Calculate Total for a Date RangeFor Excel 2007, and later versions, you can use the SUMIFS function to calculate a total based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:
Here is the formula that is entered in cell D5: =SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$2, $A$2:$A$9,"<=" & $E$2)
In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar. To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2. Use SUMIF to Calculate Total for a Date RangeFor Excel 2003, and earier versions, you can use the SUMIF function to calculate a total based on a single criterion. We'll use one SUMIF formula to total all the units where the sales date is:
Then we'll use another SUMIF formula to subtract any values where there date is
Here is the formula that is entered in cell D5: =SUMIF($A$2:$A$9,">=" &$D$2,$B$2:$B$9)
In this example, the result for the selected date range is a total of 494 units sold. To verify, you can select cells B3:B6, and look at the total shown in Excel's Status Bar. To get the total units for a different date range, change the Start date in cell D2, and/or the End date in cell E2. Sum a Filtered List With SUBTOTALAfter you filter the rows in a list, you can use the SUBTOTAL function, instead of the SUM function, to sum the numbers in the visible rows.
Note: In Excel 2003 and later versions, you can use the formula: Subtotal Function NumbersThe first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. There are 11 functions that you can use as the first argument in the SUBTOTAL function. The list is alphabetical, so that might help you remember some of the numbers, without going to Excel's Help every time. The functions are each listed twice. The first group of functions is numbered 1-11. The functions are each listed twice. The second group of functions is numbered 101-111. Sum a Filtered List With AGGREGATEThe AGGREGATE function, introduced in Excel 2010, is similar to the SUBTOTAL function, but it has more functions, and can ignore error values, as well as hidden rows in the data. Watch this video to see the steps for setting up an AGGREGATE formula, and the written instructions are below the video. Sum a Filtered List With AGGREGATEAfter you filter the rows in a list, you can use the AGGREGATE function, instead of the SUM function, to sum the numbers in the visible rows. This function was introduced in Excel 2010. Similar to the SUBTOTAL function, AGGREGATE ignores hidden rows, and offers several functions, like SUM or AVERAGE, for the selected data. However, it has 19 functions, compared to SUBTOTAL's 11 functions. Unlike the SUBTOTAL function, AGGREGATE can be set to ignore errors, as well as hidden rows, and nested SUBTOTAL and AGGREGATE functions. To sum the values in a filtered list, and ignore hidden rows and errors:
The completed formula is: =AGGREGATE(9,3,D2:D7) Sum Specific Items in Filtered ListExample 1Laurent Longre created a formula that lets you work with visible rows after a filter. For information see, Power Formula Technique in this article at John Walkenbach's web site (no longer available). Incorporating that technique, SUMPRODUCT can be used to sum visible items in a filtered table. In the following example, column D has been filtered for amounts greater than 100. The following formula will sum the Total amounts, in rows that contain "Pen" in column A.
Example 2For another example of using SUMPRODUCT and SUBTOTAL together, see my blog post, Subtotal and Sumproduct with Filter. Sam shared his technique for doing additional sums or counts, based on the visible data in a filtered table. Sam's workbook has a list with Product, Region and Amount fields. He created dynamic named ranges for the entries in each field, using INDEX and COUNTA. You can get Sam's workbook in the Downloads section, below. Get the Sample Files
How to Sum in Excel - Topic ListHere are the How to Sum topics covered on this page More Function TutorialsFunctions List Calculation Options Formulas, Getting Started SUBTOTAL Function AVERAGE COUNT / COUNTIF What is the most efficient way to access multiple worksheets in the same spreadsheet file?View multiple worksheets at the same time
If the worksheets that you want to view are in the same workbook, do the following: Click a worksheet that you want to view. On the View tab, in the Window group, click New Window. Repeat steps 1 and 2 for each sheet that you want to view.
What is the most common way to select multiple cells?To select a range, select a cell, then with the left mouse button pressed, drag over the other cells. Or use the Shift + arrow keys to select the range. To select non-adjacent cells and cell ranges, hold Ctrl and select the cells.
What is the most efficient way of adding multiple cells?The quickest and easiest way to sum a range of cells is to use the Excel AutoSum button. It automatically enters an Excel SUM function in the selected cell. The SUM function totals one or more numbers in a range of cells. Select the blank cell in the row below the cells that you want to sum, cell A5 in this example.
What is the most efficient way to select multiple adjacent cells in your spreadsheet?What is the most efficient way to select multiple adjacent cells in your spreadsheet? Press and hold the CTRL key while clicking the mouse to select the range you want.
|