How to Count Filtered Rows in Excel: SUBTOTAL Formula

How to Count Filtered Rows in Excel: SUBTOTAL Formula


Learn the smart way to count filtered rows in Excel. Our in-depth tutorial covers multiple methods like SUBTOTAL, AGGREGATE, and VBA to ensure you always have the most accurate data count.


Let’s say you have a filtered data as below:

subtotal

But when you sort it, you will realize the number is incorrect.

numbering

Let’s fix this by using SUBTOTAL formula!

What’s SUBTOTAL? 

The SUBTOTAL function in Excel is a multi-purpose function designed to perform a wide array of calculations on a specified range of cells. It’s essentially a collection of 11 functions rolled into one, giving you the ability to perform calculations such as AVERAGE, COUNT, SUM, MAX, MIN, among others. One of the unique features of the SUBTOTAL function is its ability to dynamically update the calculations based on filtered data, ignoring hidden rows if needed.

Syntax:

=SUBTOTAL(function_number, range1, [range2], …)

what’s Function number means? 

This is a code that tells Excel which function to perform. The SUBTOTAL function in Excel uses specific function numbers to designate what kind of calculation you want to perform on a specified range. These numbers come in two sets: one set that includes hidden rows (1-11) and another set that excludes hidden rows (101-111).

  • Function Numbers That Include Hidden Rows (1-11)
  • function numbers
  • Function Numbers That Exclude Hidden Rows (101-111)
  • For example, if you want to find the average of a range of cells while ignoring any hidden rows, you can use the formula =SUBTOTAL(101, range). Here, 101 is the function number for calculating the average, and it will exclude any rows hidden by a filter.
  • If you are counting the number of non-empty cells in a range and want to exclude hidden rows, you would use =SUBTOTAL(103, range). The 103 signifies COUNTA and will only count the visible cells in the range.
  • function numbers from 100
  • hidden

How to Count Filtered Rows in Excel

  • using SUBTOTAL formual
  • =SUBTOTAL(103, The 103 signifies COUNTA and will only count the visible cells in the range.
  • =SUBTOTAL(103,C2:C:2) use ‘$’ to create absolute reference.

subtotal

Do the same thing for the entire column.

You will notice it will count from C2 to each row.

second row  last row

Here is the result!

before after

it works with other states too. easy right?!

Ohio


By thoroughly understanding how to use the SUBTOTAL formula, you can count filtered rows in Excel effortlessly, allowing for more effective and accurate data analysis.

Leave a Comment