How To Add Certain Cells In Excel

Excel Sum Office Examples

How to sum in Microsoft Excel, with a simple SUM role, or formulas that sum based on criteria.

For quick overview, watch video: seven ways to sum in Excel. Side by side, scroll downward for more than videos, written steps, and free Excel workbooks.

Overview: seven Means to Sum in Excel

For a quick overview of 7 different means to sum numbers with Excel functions, you lot can sentinel this 9-infinitesimal video. At that place are written steps and screen shots below the video, for all of the examples.

Video Timeline (bluish links go to written steps beneath the video)

• 0:00 Introduction
• 0:09 1. Quick Grand Totals -- K Total a range of cells
• 0:45 2. Sum Specific Cells -- Sum a unproblematic range of cells
• one:56 3. Running Total -- Running Total
• 3:28 4. Sum Specific Items -- Sum cells that match one benchmark
• four:41 5. Sum Partial Match -- Match with wildcard
• 6:21 half dozen. Sum With Multiple Criteria -- Sum cells that friction match multiple criteria
• vii:46 seven. Sum in Filtered List -- Sum a Filtered List With SUBTOTAL
• 09:27 Go the Sample File -- Download the 7 Ways to Sum sample file

Sum a range of cells -- SUM Part

The quickest and easiest way to sum a range of cells is to use the Excel AutoSum push. It automatically enters an Excel SUM role in the selected cell. The SUM function totals one or more numbers in a range of cells.

The first example, beneath, shows how to utilize the AutoSum characteristic

1. Select the bare prison cell in the row beneath the cells that you want to sum, cell A5 in this example.

   

2. Click the AutoSum command on the Ribbon's Home tab,
   or utilize the keyboard shortcut: Alt + =

   

3. A SUM formula volition appear in the active cell, with a reference to the cells to a higher place. In the screen shot beneath, there is a SUM formula in cell A5: =SUM(A1:A4)
   NOTE: If all cells are not automatically included, you can extend the frame, to select them.

   

4. Printing the Enter cardinal to complete the entry.

The SUM Function Setup

Instead of using the AutoSum command to insert the SUM function, y'all tin can type the function manually.

The SUM function setup (syntax) is: SUM(number1, [number2],...).

• It has one required argument: number1
• It too has optional arguments (enclosed in foursquare brackets): [number2],..

These arguments tin exist jail cell references, or can be typed into the formula.

In the instance above (=SUM(A1:A4)), at that place is i argument -- a reference to cells A1:A4.

Gear up numbers that don't add up

Some 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. Spotter this brusk video tutorial, to see the steps

For the written steps, and other ways to fix the trouble, go to the Numbers Don't Add Up page.

K Total a range of cells

In one quick step, you tin calculate the row, column, and grand totals for a range of cell. Picket this short Excel G Totals video, to see how to practise it. There are written instructions below the video.

Quick K Total for a range of cells

1. Select the range of cells, and the blank row beneath the range, and the blank cells in the column to the right (cells A1:D5 in the example below)



2. Click the AutoSum push button on the Ribbon's Home tab. A SUM formula will be automatically entered for each Total.

Running Total

To come across a running total in each row of an Excel list, you can use the SUM office, with the starting row locked as an accented reference. In that location are slightly different steps below,

• for a running total in a worksheet list
• for a running total in a named Excel table

Running Full in Worksheet List

For a worksheet list (not a named Excel table), this video shows how to ready upwards the running total formula, and lock the starting row. The written steps are below. For the video transcript, get to the Running Total Video page.

Running Total - Worksheet Listing

For 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.

• Enter this formula in cell D2
  • =SUM(C$2:C2)
• Copy the formula downwardly to cell D6

How the Formula Works

The formula uses an absolute reference to row 2 every bit the starting point -- C$2 -- and a relative reference to the ending point -- C2

This ensures that the starting point will not modify when yous copy the formula downward to the rows beneath. Here is the formula in jail cell D6 -- the starting point has stayed the same and the ending point is in the current row -- C6

• =SUM(C$2:C6)

Running Total in Excel Table

For a named Excel tabular array, nosotros tin't use the worksheet listing formula in the previous section. Outset, I'll show you the problem with that formula, and and then you lot'll see the formula that works in a named table.

Trouble with Running Total

Later on yous enter the formula in cell D2 of the named Excel tabular array, it automatically fills downward, and the running total looks correct.

Only, every bit before long 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:

• = SUM(C$2:C6)

As soon every bit the next entry was started in row 7, the formula in D6 automatically changed. At present information technology has an wrong ending reference to C7, instead of C6:

• =SUM(C$2:C7)

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 Tabular array

To avoid that problem, we'll utilize a slightly different formula for a running total in a named Excel table.

In the screen shot beneath, amounts are entered in column C, and a running total is calculated in column D.

• Enter this formula in cell D2
  • =SUM(C$1:[@Amt])
• The formula automatically fills down to cell D6
• All cells in column D prove the aforementioned formula

How the Formula Works

The formula uses an absolute reference to a heading cell as the starting bespeak -- C$one

• To prevent issues if a new data row is added at the pinnacle, the starting prison cell is in the table heading row
• Considering the heading cell contains text, its value is treated equally zero, and won't bear upon the running total

For the ending betoken, there is a structured tabular array reference -- [@Amt]

• This is a reference to the Amt cell in the current row
• With that tabular array cell reference, there's no trouble when new rows are added to the Excel table

This screen shot shows that when a new row is started, cell C6 continues to testify its original formula, and the running full amounts are correct in each row

Sum a range of cells -- Kickoff

If yous insert a row directly above the SUM role in the previous example, the new row may not be included in the SUM. It may proceed to sum cells A1:A4, and ignore A5. To ensure that new rows are included in the total, y'all can utilise the Outset function with the SUM function.

1. Select jail cell A5.
2. Enter the following formula:
   =SUM(A1:OFFSET(A5,-ane,0))



3. Press the Enter cardinal to complete the entry.
4. Insert a row above row 5
5. Blazon a number in cell A5, and it will be included in the total in cell A6

Sum cells that match criteria -- SUMIF

Here are iii means to sum cells that friction match criteria, using the Excel SUMIF function:

    - Friction match criterion exactly

    - Match criterion in a string

- Match criterion using operator

SUMIF Part Setup

The SUMIF function setup (syntax) is: SUMIF(range, criteria, [sum_range])

• SUMIF has 2 required arguments:
  • range - cells to cheque for the criteria
  • criteria - value to apply as criteria
• SUMIF also has one optional argument (in foursquare brackets):
  • [sum_range] - cells to sum -- if omitted, values in range are summed

These arguments can exist jail cell references, or can be typed into the formula.

Friction match benchmark exactly

You can summate a total for rows that meet a specific criterion. In this example only the rows with Pen orders volition be included in the full.

1. Select the cell in which you desire to run into the total
2. Type an equal sign (=) to start the formula
3. Type:   SUMIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
5. Type a comma, to split the arguments
6. Type the criterion. In this example, you're checking for text, so type the word in double quotes:   "Pen"
   Note: upper and lower case are treated equally
   
7. Type a comma, to split up the arguments
8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
9. The completed formula is:
    =SUMIF(A2:A10,"Pen",B2:B10)
   
10. Printing the Enter primal to consummate the entry

    

Annotation: Instead of typing the criterion in a formula, yous can refer to a cell. For instance, the formula in footstep 9 higher up could be changed to:
=SUMIF(A2:A10, B12, B2:B10)
if jail cell B12 contained the text — pen.

Match criterion in a string

You can add cells that contain a criterion as role of the cell'southward contents. In this example all Pen, Gel Pen, and Pencil orders volition exist summed, because they contain the string "pen".

1. Select the cell in which yous want to see the full (prison cell A12 in this example)
2. Type an equal sign (=) to start the formula
3. Blazon:   SUMIF(
4. Select the cells that contain the values to check for the criterion. In this example, cells A2:A10 will be checked
5. Type a comma, to carve up the arguments
6. Blazon the criterion. In this example, you're checking for text, then blazon the word in double quotes, with one or more asterisk (*) wildcard characters:   "*Pen*"
   Note: upper and lower case are treated every bit
   
7. Type a comma, to separate the arguments
8. Select the cells that contain the values to sum. In this example, cells B2:B10 will be summed
9. Type a closing bracket. The completed formula is: =SUMIF(A2:A10,"*Pen*",B2:B10)
   
10. Press the Enter central to consummate the entry
11. The result will be 53, the total of rows that contain the string, "Pen"

    

Annotation: Instead of typing the benchmark in a formula, you can refer to a cell. For example, the formula in pace ix higher up could exist changed to:
=SUMIF(A2:A10,"*" & B12 & "*",B2:B10)
if cell B12 contained the text — pen.

Criterion and operator

Yous can utilize an operator with a criterion. In the examples below, meet how to combine them in the SUMIF formula.

Example 1

Example ii

Example 1 - Sum Rows Greater Than Gear up Amount

In this case only the rows where the number of sales reps is greater than or equal to ten will be included in the total.

1. Select the jail cell in which you desire to see the full
2. Type an equal sign (=) to offset the formula
3. Type:   SUMIF(
4. Select the cells that contain the values to check for the benchmark. In this example, cells B2:B10 will exist checked
5. Blazon a comma, to separate the arguments
6. Type the criterion. In this case, you're checking for rows where the number of visits is greater than or equal to 10. The >= operator is used before the number, and the entire benchmark is enclosed in double quotes.
7. Type a comma, to separate the arguments
8. Select the cells that contain the values to sum
9. Type a closing bracket. The completed formula is:
    =SUMIF(B2:B10,">=x",C2:C10)
   
10. Press the Enter key to complete the entry
11. The result will be 183, the full of rows with x or more sales reps.

    

Note: Instead of typing the benchmark in a formula, you can refer to a cell. For example, the formula in pace nine above could be changed to:
=SUMIF(B2:B10,">=" & B12,C2:C10)
if prison cell B12 independent the number — 10.

Case 2 - Rolling 12 Calendar month Total

In this instance only the rows for the previous xi months, and the electric current month, will be included in the total. This creates a Rolling Full.

The dates are in column A, and the list must exist sorted by engagement. The monthly amounts are in cavalcade B.

1. Select the first cell in which you want to see the rolling total -- cell C2 in this example
2. Enter the following formula, and press Enter:
   =SUMIF(A$2:A2,">=" & Engagement(YEAR(A2),MONTH(A2)-11,DAY(A2)),B$ii:B2).
3. Re-create the formula downwardly to the concluding row with data.
4. Each row shows the Rolling Total for the latest 12 months (if available)

How It Works

The formula checks the dates in column A, starting in row 2 (A$2), and downwards to the electric current row (A2)

=SUMIF(A$2:A2

The DATE part calculates the date that is xi months prior to date in current row

DATE(YEAR(A2),MONTH(A2)-xi,DAY(A2))

The >= operator checks for dates that are greater than or equal to that date,

">=" & DATE(Year(A2),Month(A2)-11,Twenty-four hour period(A2))

For rows that run across 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 friction match multiple criteria -- SUMIFS

In Excel 2007 and later versions, you can utilize the SUMIFS function to summate a total for rows that meet 2 or more criteria. Lookout man this short video to see the steps. At that place are written instructions below the video.

For the full video transcript, go to the Sum Amounts With 2 Criteria Video page.

SUMIFS Function Setup

The SUMIFS function setup (syntax) is: SUMIFS(range, criteria, [sum_range])

• SUMIFS has 3 required arguments:
  • sum_range - cells to cheque for the criteria
  • criteria_range1 - cells to check for the commencement criterion
  • criteria1 - value to use every bit first criterion
• Additional pairs of criteria_range and criteria tin can be added, to sum based on multiple criteria

These arguments can be cell references, or tin can be typed into the formula.

Friction match multiple criteria

In this instance, the SUMIFS role will sum the amounts in cavalcade D, based on ii criteria:

• the status in column B is "Shipped"
• number of units in column C is greater than or equal to 10

Those criteria are entered in cells F6 (Shipped) and G6 (10)

Follow these steps to create the SUMIFS formula:

1. Select the cell in which you want to see the full
2. Type an equal sign (=) to start the formula
3. Blazon:   SUMIFS(
4. Select the cells that incorporate the values to sum. In this example, cells D3:D10 will be summed
5. Type a comma, and so select the cells that comprise the values to check for the first criterion. In this example, cells B3:B10 will be checked
6. Type a comma, and then type the offset criterion, "Shipped"
7. Type a comma, then select the cells that contain the values to cheque for the second criterion. In this example, cells B2:B6 will exist checked
8. Blazon a comma, so type the second criterion:  " >=" & 10
9. Cease with a closing bracket: )
10. The completed formula is:
    =SUMIFS(D3:D10,B3:B10,"Shipped",C3:C10,">=" & 10)
11. Press the Enter key to complete the entry

Formulas With Table References

If y'all create formulas with tabular array references, and and so try to re-create those formulas to adjacent columns, you might see bug. This video shows the problem, and ii means to prevent it. At that place are written steps beneath the video.

Problems with Tabular array References

In this sales summary, in that location is a SUMIFS formula in jail cell C5, which is showing the correct total for Bars sales in the Eastward region.

=SUMIFS(Sales_Data[Quantity], Sales_Data[Region],$B5, Sales_Data[Category],C$4)

Even so, if you indicate to the make full handle in cell C5, and elevate to the right, the formula shows an wrong full in prison cell D5.

If you check the formula in cell D5, all of the table references have shifted one column to the correct, considering the formula was dragged 1 column to the right.

=SUMIFS(Sales_Data[TotalCost], Sales_Data[Category],$B5, Sales_Data[Production],D$4)

• Instead of Quantity, the formula is summing the Full Cost column
• Instead of looking for Eastward in the Region column, it's looking in Category
• Instead of looking for Cookies in the Category column, it's looking in Product

None of those criteria are found, so the result is zero.

To prevent this problem of shifting table references, don't drag the make full handle to re-create beyond.

Instead, utilize one of the following methods:

• Fill Right
• Copy and Paste

Fill Right

• Select the prison cell with the formula, and the cells to the right, where you want to copy the formula
• Press Ctrl+R to fill the formula to the correct

Re-create and Paste

• Select the cell with the formula, and press Ctrl+C to copy it
• Select all the cells where y'all want to copy the formula
• Press Ctrl+5 to paste the formula

SUMIFS With Multiple AND/OR Criteria

In the list below, you could apply a SUMIFS formula to total the rows where:

• City is New York

AND

• Category is Bars

The formula in cell G9 would be:

• =SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6)

Formula with Multiple Criteria for AND / OR

Instead of just one category though, we would like to calculate the full for ii or more categories. In this instance, nosotros'll calculate the total where:

• city is New York AND Category is Cookies

OR

• city is New York AND Category is Bars

The ii categories are entered in cells G6:G7 on the worksheet.

Array-Entered Formula

To calculate the full, nosotros'll wrap the SUMIFS formula with a SUM part, and enter the formula equally an array.

1. Select the cell in which you want to see the total -- G9 in this case
2. To start the formula, type:=SUM(SUMIFS(
3. Select the cells that contain the values to sum. In this example, cells D4:D15 volition be summed
4. Type a comma, then select the cells that incorporate the values to cheque for the first benchmark. In this example, cells B4:B15 will be checked -- they contain the city names
5. Type a comma, and so click on the jail cell with the showtime criterion -- G4
6. Type a comma, then select the cells that incorporate the values to bank check for the second criterion. In this example, cells C4:C15 volition be checked -- they have the category
7. Type a comma, and then select the cells with the list for the 2nd benchmark -- G6:G7
8. Terminate with 2 endmost brackets: ))
9. The completed formula in cell G9 is:
   =SUM(SUMIFS(D4:D15,B4:B15,G4,C4:C15,G6:G7))
   
10. Printing the Ctrl + Shift + Enter keys to array-enter the formula -- the result will non be correct if you simply printing the Enter primal.
11. Curly brackets volition exist automatically added at the beginning and end of the formula, to show that information technology is assortment-entered. Practice not type this brackets yourself.

Sum Cells Lucifer Multiple Criteria -- SUMPRODUCT

Lucifer multiple criteria

In Excel 2003 and before, yous can use the SUMPRODUCT part to calculate a total for rows that encounter two or more criteria. If you're using Excel 2007 or later, you lot should use the SUMIFS function, every bit described in the previous section.

In this instance only the rows where the condition is "Active" and the number of visits is greater than or equal to ten will be included in the total.

1. Select the jail cell in which you desire to run into the total
2. Blazon an equal sign (=) to start the formula
3. Type:   SUMPRODUCT(--(
4. Select the cells that contain the values to check for the showtime benchmark. In this example, cells A2:A6 will exist checked
5. Type the commencement criterion:   ="Active"
6. Type ),--(
7. Select the cells that contain the values to check for the second benchmark. In this example, cells B2:B6 will be checked
8. Type the second criterion:   >=10
9. Type ),--(
10. Select the cells that contain the values to sum. In this example, cells C2:C6 will exist summed
11. Stop with closing brackets: ))
12. The completed formula is:
    =SUMPRODUCT(--(A2:A6="Active"), --(B2:B6>=x),--(C2:C6))
    
13. Press the Enter key to complete the entry

Sum Top 5 Numbers in List

Use the SUM part and Big functions together, to add the largest numbers in the listing.

Version one -- Few Top Numbers

If a few numbers are to be summed, eastward.g. top 3, you can type the numbers into the formula. For example:

=SUM(Large(A1:A7,{ane,two,iii}))

The result is 70+60+50 = 180

Note: The second l is not included in the event, even though information technology is tied for tertiary place.

Version two -- Many Top Numbers

If many top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function. In the INDIRECT office, apply row numbers that represent the numbers you want to include. In this instance, rows one:10 are used, then the pinnacle x numbers in the referenced range will be summed.

1. Type the formula:
   =SUM(Big(A1:A50,ROW(INDIRECT("1:ten"))))
2. This is an array formula, and must exist array-entered. To practice this, concord the Ctrl and Shift keys, and press Enter

Version 3 -- Variable Pinnacle Numbers

If a variable number of top numbers are to exist summed you tin include the INDIRECT function in the formula with the SUM function, as shown in a higher place, and refer to a cell that holds the variable..

1. In cell C1, type the number of top cells, east.g. 10
2. Blazon the formula:
   =SUM(LARGE(A1:A7,ROW(INDIRECT("ane:"&C1))))
3. This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter

Sum Amounts in a Date Range

To sum amounts based on a date range, you lot can use the SUMIFS function in Excel 2007 or later versions. Sentinel this video to come across the steps, and the written instructions are below the video.

Sum Amounts in a Date Range

To full the amounts in a specific engagement range, apply the SUMIFS function (Excel 2007 and subsequently) or the SUMIF function. There are two examples below:

    - Total with SUMIFS

    - Total with SUMIF

In this case, a Start appointment and an End appointment are entered on the worksheet. Dates are in column A, and units sold are in cavalcade B.

Run across more than Appointment Range examples on the Sum or Count for a Appointment Range page.

Use SUMIFS to Calculate Full for a Engagement Range

For Excel 2007, and later on versions, you tin use the SUMIFS function to calculate a full based on multiple criteria. We'll use a SUMIFS formula to total all the units where the sales date is:

• on or later the Start engagement
  • AND
• on or before the End engagement.

Here is the formula that is entered in prison cell D5:

=SUMIFS($B$2:$B$9,$A$2:$A$9,">=" & $D$ii, $A$2:$A$9,"<=" & $E$2)

• The first argument, $B$2:$B$nine , is the range with the numbers that nosotros want to sum.
• The 2nd statement, $A$2:$A$9 , is the range to check for criteria one.
• The tertiary statement, ">=" & $D$2 , is the range with the value for criteria 1 (the Start engagement), and the operator to employ with that value (greater than or equal to)
• The 4th argument, $A$2:$A$9 , is the range to check for criteria 2.
• The 5th argument, "<=" & $E$2 , is the range with the value for criteria ii (the End engagement), and the operator to use with that value (less than or equal to)

In this example, the upshot for the selected appointment range is a total of 494 units sold. To verify, you tin can select cells B3:B6, and expect at the total shown in Excel's Condition Bar.

To go the total units for a different date range, change the Start date in cell D2, and/or the End appointment in cell E2.

Apply SUMIF to Summate Total for a Date Range

For Excel 2003, and earier versions, you lot tin use the SUMIF part to calculate a total based on a unmarried criterion. We'll utilize one SUMIF formula to total all the units where the sales date is:

• on or after the Offset engagement

And so nosotros'll use some other SUMIF formula to subtract any values where at that place date is

• after the Cease date.

Here is the formula that is entered in cell D5:

=SUMIF($A$2:$A$9,">=" &$D$2 ,$B$two:$B$9)
- SUMIF($A$2:$A$nine,">" &$E$2,$B$2:$B$ix)

• The range, $A$2:$A$9 , contains the numbers that we want to sum.
• The criteria, ">=" & $D$2 , is the range with the Beginning date, and the operator to employ with that value (greater than or equal to)
• The range, $B$2:$B$ix , is the range to bank check for the appointment
• The criteria, ">" & $E$2 , is the range with the Finish engagement, and the operator to use with that value (greater than)

In this instance, the result for the selected date range is a total of 494 units sold. To verify, you tin can select cells B3:B6, and expect at the total shown in Excel's Condition Bar.

To become the full units for a different appointment range, change the Start appointment in cell D2, and/or the Terminate date in cell E2.

Sum a Filtered List With SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL part, instead of the SUM function, to sum the numbers in the visible rows.

1. Employ an AutoFilter to the table. There are instructions hither -- AutoFilter Basics
2. Filter at to the lowest degree one of the columns in the table. In this example, the kickoff cavalcade has been filtered for Binders.
3. Select the jail cell immediately below the column you want to sum.
4. Click the AutoSum push button on the Ribbon'south Home tab.
   • If you want the SUBTOTAL function in a cell other than the one straight below the filtered list, you can type the formula, instead of using the AutoSum push.
5. A SUBTOTAL formula volition be automatically inserted, totalling the visible cells in the column
• The outset statement in the SUBTOTAL office is a office number, that specifies how the numbers should be calculated. There is a nine in this example, which tells Excel to SUM the numbers.
• Other function numbers tin can be used, such as 1 for Average, and 3 for COUNTA. The full list is shown in my web log post Total a Filtered List.
6. Press the Enter fundamental to complete the formula entry.

Annotation: In Excel 2003 and later versions, yous can apply the formula:
    =SUBTOTAL(109,B2:B9)
to subtotal visible cells in a range where rows have been manually subconscious, or filtered.

Subtotal Function Numbers

The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. In that location are 11 functions that you tin use as the first statement in the SUBTOTAL function. The listing is alphabetical, so that might help y'all recollect some of the numbers, without going to Excel's Help every time.

The functions are each listed twice. The offset group of functions is numbered one-eleven.

The functions are each listed twice. The 2nd group of functions is numbered 101-111.

Sum a Filtered List With Amass

The AGGREGATE function, introduced in Excel 2010, is similar to the SUBTOTAL function, but it has more functions, and tin ignore mistake values, as well as hidden rows in the data.

Watch this video to see the steps for setting upwardly an AGGREGATE formula, and the written instructions are below the video.

Sum a Filtered List With Aggregate

After you filter the rows in a list, you can utilize the Aggregate function, instead of the SUM part, to sum the numbers in the visible rows. This role was introduced in Excel 2010.

Like to the SUBTOTAL function, AGGREGATE ignores subconscious rows, and offers several functions, like SUM or Boilerplate, for the selected information. Yet, information technology has 19 functions, compared to SUBTOTAL's 11 functions.

Unlike the SUBTOTAL function, Aggregate can be set to ignore errors, as well as subconscious rows, and nested SUBTOTAL and Amass functions.

To sum the values in a filtered listing, and ignore subconscious rows and errors:

1. Select the jail cell where you lot want the sum
2. Type =AGGREGATE(
3. In the listing of functions, double-click on 9 - SUM, to add together 9 as the commencement statement.
4. Type a comma, and in the listing of options, double-click on selection iii or option 7. In this instance, 3 is the second argument, and the event will ignore hidden rows, errors, and nested AGGREGATE and SUBTOTAL functions.

   

5. Type a comma, and select the range of cells that contain the information -- D2:D7 in this instance.
6. Type a bracket, to complete the formula, and press the Enter key.

The completed formula is: =Amass(9,3,D2:D7)

Sum Specific Items in Filtered List

Case 1

Laurent Longre created a formula that lets you work with visible rows afterwards a filter. For information come across, Power Formula Technique in this commodity at John Walkenbach's spider web site (no longer bachelor).

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 volition sum the Full amounts, in rows that incorporate "Pen" in column A.

• Filter column D for amounts greater than 100.
• In jail cell A12, type: Pen
• In cell B12, enter the following formula:
  • =SUMPRODUCT(SUBTOTAL(iii,Starting time(A1:A10,ROW(A1:A10)
    -MIN(ROW(A1:A10)),,1)), --(A1:A10=A12),D1:D10)
• Press the Enter key to complete the formula entry.

Example 2

For another example of using SUMPRODUCT and SUBTOTAL together, see my blog mail, Subtotal and Sumproduct with Filter. Sam shared his technique for doing boosted 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'due south workbook in the Downloads section, below.

Become the Sample Files

• SUM Examples: Download the zipped Sum functions sample workbook. The workbook contains the examples for SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and AGGREGATE functions. The zipped file is in xlsx format, and does non comprise any macros.
• SUMIFS Orders: Download the SUMIFS Orders sample workbook, to follow forth with the Sum Amounts with 2 Criteria video. The zipped file is in xlsx format, and does not contain any macros.
• 7 Ways to Sum: Download the 7 Ways to Sum sample workbook, to follow along with the vii Ways to Sum video. The zipped file is in xlsx format, and does not contain any macros.
• Sam'due south Workbook: To run into Sam's workbook and formulas, for doing boosted sums or counts, based on the visible data in a filtered table, download the SUMPRODUCT SUBTOTAL sample file.
• Tabular array References: To see the problem with copying formulas with table references, download the Table Reference Problem workbook. The zipped file is in xlsx format, and does non contain any macros

More Function Tutorials

Functions List

Calculation Options

Formulas, Getting Started

SUBTOTAL Function

AVERAGE

COUNT / COUNTIF

Source: https://www.contextures.com/xlfunctions01.html

Posted by: ortegoounge1939.blogspot.com

Share this post