Home > Formulas > Count > Filtered Rows Excel Filtered Rows - Count or SumCount 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 ListTo 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 ListAfter 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) Count Rows in Filtered List - SUBTOTALAfter you filter the rows in a list, you can use the SUBTOTAL function to count the visible rows in the filtered data.
SUBTOTAL Syntax
In the SUBTOTAL function syntax, there are two required arguments:
SUBTOTAL ExampleIn 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 FormulaIn the above formula:
Filtered or Manually HiddenIn 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:
|
2) Count Rows With AGGREGATEAfter 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.
The two visible numbers are counted, and the error in cell D9 is ignored. |
3) Count Specific Items in Filtered ListLaurent 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.
Set Up SUMPRODUCT FormulaTo create the criteria cell and formula, follow these steps:
|
4) Count Unique Items in Filtered ListHere are two ways that you can count the number of unique items in a filtered list:
a) Helper Columns for Unique Visible Item CountThanks 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 FilterHowever, 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 RowsNext, 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 ItemsFinally, 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 CountIn 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.
|
Sum Numbers in Filtered ListAfter 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) Sum 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 of filtered cells.
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. |
2) Sum 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)
|
3) 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
|
More Function TutorialsCount Criteria in Other Column |
Last updated: February 24, 2023 3:34 PM