Home > Formulas > Count > Filtered Rows

Excel Filtered Rows - Count or Sum

Count filtered rows in Excel list. How many rows are visible in Excel table? Sum visible numbers in filtered table. Short videos, Excel files, written steps

Sum or Count in Filtered List

To sum or count in a filtered list, use the AGGREGATE function, which was introduced in Excel 2010, This function only includes values in the visible rows of a filtered list, similar to the Excel SUBTOTAL function.

However, AGGREGATE has more functions than SUBTOTAL, and you can set it to 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, to count with AGGREGATE or to sum with AGGREGATE.

Count Rows in Filtered List

After you filter the rows in a list, you can use functions to count only the visible filtered rows.

See the examples below for these ways to count items in a filtered list.

1. For a simple count of visible numbers or all visible data, use the SUBTOTAL function
2. To count visible data, and ignore errors, use the AGGREGATE function
3. To count specific items in a filtered List, use a SUMPRODUCT formula
4. To count UNIQUE items in a filtered List, use an Array-entered formula

1) Count Rows in Filtered List - SUBTOTAL

After you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows in the filtered data.

• NOTE: For more examples for how to count rows in a filtered list, go to the Excel Filtered Rows Count or Sum page.

SUBTOTAL Syntax

• SUBTOTAL(function_num,ref1,[ref2],...)

In the SUBTOTAL function syntax, there are two required arguments:

• function_num: (required) number that specifies which function to use for the subtotal.
• For details on the function numbers, go to the Excel SUBTOTAL function page.
• ref1: (required) first range of cells that you want to subtotal

SUBTOTAL Example

In this example, there is a list on the worksheet, from cell A1 to D10.

In column A, a filter has been applied, to show Binder Items only.

In cell A12, the following formula is entered:

• =SUBTOTAL(3,D2:D10)

SUBTOTAL Formula

In the above formula:

• first argument (function_num) is 3 (COUNTA)
  • both text and numbers will be counted
• second argument (ref1) is the range D2:D10
  • values in the visible rows of that rante will be counted

Filtered or Manually Hidden

In the first set of function numbers for the SUBTOTAL function, only the rows hidden by filtering are ignored.

If you also want to ignore rows that were manually hidden, use the "100-range" numbers instead.

In the screen shot below:

• Function 1: AVERAGE - will NOT ignore any manually hidden rows
• Function 101: AVERAGE - WILL ignore any manually hidden rows

2) Count Rows With AGGREGATE

After you filter the rows in a list, you can use the AGGREGATE function to count the visible rows. This function was introduced in Excel 2010, and is similar to SUBTOTAL, but it has 19 functions, compared to SUBTOTAL's 11 functions. Another advantage is that it can ignore errors, as well as hidden rows.

1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
3. Select the cell in which you want to see the total -- cell B1 in this example
4. To start the formula, type:   =AGGREGATE(
5. In the list of function numbers, double-click on 3-COUNTA, then type a comma
6. In the list of option numbers, double-click on 3 - Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions, then type a comma
7. Select the cells that contain the values to check for the first criterion. In this example, the Total column in the table is selected.
8. Type a closing bracket, then press the Enter key to complete the formula entry.
   =AGGREGATE(3,3,Table1[Total])

The two visible numbers are counted, and the error in cell D9 is ignored.

3) Count Specific Items in Filtered List

Laurent 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 (via the WayBack Machine site)

Incorporating that technique, SUMPRODUCT can be used to count visible items in a filtered table.

• In the following example, column D has been filtered for amounts greater than 100.
• The formula in cell B12 counts the number of visible rows that contain "Pen" in column A.

Set Up SUMPRODUCT Formula

To create the criteria cell and formula, follow these steps:

1. From the drop down list in cell D1, select Custom.
2. Filter for rows greater than 100.
3. In cell A12, type: Pen
4. In cell B12, enter the following formula:

• =SUMPRODUCT(SUBTOTAL(3,OFFSET(A1:A10,ROW(A1:A10)
                -MIN(ROW(A1:A10)),,1)), --(A1:A10=A12))

5. Press the Enter key to complete the formula entry.

4) Count Unique Items in Filtered List

Here are two ways that you can count the number of unique items in a filtered list:

1. Helper Columns: Add extra "helper" columns in your list, to check each row for a unique item. Then, use that column to get the total count of unique items. This method is easier to understand, but on some worksheets you might not want to add an extra item
2. Array Formula: If you can't add helper columns to your list, use a complex array-entered formula instead. This can be entered in a single cell on the worksheet, so it requires less space. However, the formula is more difficult to understand.

a) Helper Columns for Unique Visible Item Count

Thanks to Alex J who shared his technique for counting the unique visible items in a filtered list.

In the screenshot below, the formula in column D shows a 1 for the first instance of each item, and a 0 for all subsequent instances. For example, in cell D7, there’s a 1, counting item 91-AB145, and in the next two rows the count for the same item is zero.

The formula in cell D5 is:

=(COUNTIF($C$5:$C5,$C5)<2)*1

Apply a Filter

However, when the list is filtered to show only the Central region items, the total in cell D2 still shows 16 unique items, and cell D11 shows a zero for item 91-AB146, even though it’s the first visible instance of that item.

To solve the problem, AlexJ added some helper columns to the table. First, in column E, he created a formula to check if the row is visible. The formula in cell E5 is:

=1*SUBTOTAL(3,$D5)

AlexJ uses the 1 multiplier at the start of the formula to avoid the problem of the last row not being included in the AutoFilter range.

Based on this new formula, the total in cell E2 correctly shows that only 6 rows are visible.

Test For Visible Rows

Next, in column F, AlexJ created a formula to show the item name if the row is visible. For hidden rows, the formula displays a hyphen instead of the item name. In cell F5, the formula is:

=IF($E5,$C5,”-“)

In cell G1, I tested the result for hidden cell F6, and you can see the result is a hyphen.

Count Unique Visible Items

Finally, in column G, AlexJ created a formula to check for unique items in column F, where only the visible rows have an item name. In cell G5, the formula is:

=($F5<>”-“)*(COUNTIF($F$5:$F5,$F5)<2)

With this formula, the hidden rows are ignored, and the count of unique items in cell G2 is correct for the filtered rows. The item 91-AB145 is counted only once, even though it’s in the filtered results twice, and item 91-AB146 is counted, even though it’s not the first instance of that item in the full list.

b) Array Formula for Unique Visible Item Count

In the Excel Expert Newsletter (issue 20, July 8, 2001 - no longer available), there is a formula to count unique items in a filtered list.

In this example, the list is filtered to show only the Central region, and unique visible items in column D are counted.

1. The LineVal column (cells C5:C27) is a named range -- Rge
2. The name unRge is defined with the following formula:
• =IF(SUBTOTAL(3,OFFSET(Rge, ROW(Rge)- MIN(ROW(Rge)),,1)), Rge,"")
3. In cell C2, enter the following formula, then press Ctrl+ Shift + Enter:

• =SUM(N(IF(ISNA(MATCH("""",unRge,0)), MATCH(Rge,Rge,0),
  IF(MATCH(unRge,unRge,0)=MATCH("""",unRge,0),0,
  MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1))

Sum Numbers in Filtered List

After you filter the rows in a list, you can use functions to sum numbers in only the visible filtered rows.

See the examples below for these ways to sum numbers in a filtered list.

1. For a simple sum of visible numbers, use the SUBTOTAL function
2. To sum visible numbers, and ignore errors, use the AGGREGATE function
3. To sum specific items in a filtered List, use a SUMPRODUCT formula

1) Sum Filtered List With SUBTOTAL

After 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 of filtered cells.

1. Apply an AutoFilter to the table. There are instructions here -- AutoFilter Basics
2. Filter at least one of the columns in the table. In this example, the first column has been filtered for Binders.
3. Select the cell immediately below the column you want to sum.
4. Click the AutoSum button on the Ribbon's Home tab.
   • If you want the SUBTOTAL function in a cell other than the one directly below the filtered list, you can type the formula, instead of using the AutoSum button.
5. A SUBTOTAL formula will be automatically inserted, totalling the visible cells in the column
• The first argument in the SUBTOTAL function is a function number, that specifies how the numbers should be calculated. There is a 9 in this example, which tells Excel to SUM the numbers.
• Other function numbers can be used, such as 1 for AVERAGE, and 3 for COUNTA. The full list is shown in my blog post Total a Filtered List.
6. Press the Enter key to complete the formula entry.

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

Subtotal Function Numbers

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

2) Sum Filtered List With AGGREGATE

After 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:

1. Select the cell where you want the sum
2. Type =AGGREGATE(
3. In the list of functions, double-click on 9 - SUM, to add 9 as the first argument.
4. Type a comma, and in the list of options, double-click on option 3 or option 7. In this example, 3 is the second argument, and the result will ignore hidden rows, errors, and nested AGGREGATE and SUBTOTAL functions.

   

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

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

3) Sum Specific Items in Filtered List

Example 1

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

• Filter column D for amounts greater than 100.
• In cell A12, type: Pen
• In cell B12, enter the following formula:
  • =SUMPRODUCT(SUBTOTAL(3,OFFSET(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 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

1. Count Functions: Download the Count Functions sample workbook. The workbook contains the Count examples for COUNT, COUNTIF, COUNTIFS, SUMPRODUCT, SUBTOTAL and AGGREGATE. The zipped file is in xlsx format, and does not contain any macros.
2. Count Unique: Download the Count Unique Items in Filtered List workbook. The zipped file is in xlsx format, and does not contain any macros.
3. SUM Examples: Download the zipped Sum functions sample workbook. The workbook contains the Sum examples for SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and AGGREGATE functions. The zipped file is in xlsx format, and does not contain any macros.
4. Sam's Workbook: To see Sam's workbook and formulas, for doing additional sums or counts, based on the visible data in a filtered table, download the SUMPRODUCT SUBTOTAL sample file.

More Function Tutorials

COUNT / COUNTIF

SUM / SUMIF

SUBTOTAL Function

Subtotal Feature

Date Range, Sum or Count

Count Criteria in Other Column

Count Specific Items

Count Specific Items in Cell

Count Cells With Specific Text

AVERAGE

Calculation Options