This formula =COUNTIFS($A$7:$A$309,$AK4,$B$7:$B$309,1) considers all rows within the given range when counting. What if we want to exclude filtered and/or hidden rows?

(Download my Excel demo file. This post dedicated to Marcelo Ribeiro Simões)

Why Do This?

I normally advise people to include all relevant criteria inside formulas. This way, formulas serve their purpose independently. Filtering and hiding rows are a separate action for viewing the data that won’t interfere with your formulas.

There are exceptions when it’s helpful to adjust formula results by quickly hiding/filtering rows instead of constantly modifying formula syntax. Imagine a busy meeting with lots of questions.

Exclude Hidden/Filtered Rows

How do we incorporate hidden and/or filtered rows into our formulas?

The common method is to use complex formulas like this:

=SUMPRODUCT((A7:A309>50)*(SUBTOTAL(103,OFFSET(A7,ROW(A7:A309)-MIN(ROW(A7:A309)),0))))

Power users can decipher this but most people can’t. What if we could reduce the formula to:

=COUNTIFS($A$7:$A$309,$AK4,$O$7:$O$309,1)

How is this possible?

The trick is to add this helper formula =–SUBTOTAL(103,A8) alongside the data-set. Column O uses the subtotal function to determine if the row is visible. 1 = visible, 0 = non visible. We then include the 1s and 0s inside formula conditions.

Helper columns increase file size but they simplify formula writing and auditing.

Demo File

The data-set is called ‘Heart Disease UCI’ from Kaggle.com. See sheet ‘Counting Example’. A to N are original columns. We have 303 rows.

Subtotal formula is in column O. Initially subtotal shows 1 for all rows but once you manually hide a row or apply a filter non visible rows will change to 0. Watch formulas in cells A6 and O6 change!

Formulas with a green background change if they can no longer see relevant rows (some rows are already disqualified via formula criteria).

Columns AL to AO are traditional formulas that are not affected by non visible rows. This let’s you compare the results.

Sheet ‘Ranking Example’ demonstrates how hiding/filtering rows can affect ranking when using a subtotal helper column.

See Filter Values!

A downside of using filters is that you can’t see filter values. You have to remember the values that you’ve filtered columns to. GOOD NEWS: you can see filter values if you use slicers! Convert your data range into a table, select any cell in your table and click ‘Insert’/’Slicer’. Yes…there are a few things to get used to but it’s great for exploring data with others as you can see filter values! See sheet ‘Filtering Example (table!)‘ in my Demo Excel file (above).

Balance

Obviously we can’t have 50 subtotal helper columns as this would increase the file size and create clutter. However 1 or maybe 2 subtotal helper columns can be extremely helpful if you want to calculate visible rows only! Take some time to experiment with this concept.

Bonus!

If you’re still with me there’s more good news! Excel’s AGGREGATE function goes above and beyond what SUBTOTAL function can do. It takes some time to learn it but it’s worth it!

Read this intro from Microsoft and then watch this playlist of videos from Mike Girvin (ExcelIsFun).

About Me

My name is Kevin Lehrbass.

I’m a Data Analyst and I live in Markham Ontario Canada.

The main reason I like the subtotal helper column is to avoid crazy long complex formulas. Why so much pain? Just add a subtotal helper column and use filters and/or slicers!