Count Visible Rows - 1034 - Learn Excel from MrExcel

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Jun 11, 2009.
Isabel asks how to count only the visible entries in a range. =COUNTA() is returning all of the values. Episode 1034 will show you how to count or sum the visible rows in a range.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically we start out with massive Amount of Data.
See how we're gonna analyze as well plus file up a pivot table.
Let's see if you can solve this problem.
All right welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's a question sent in by Isabel. Isabel is well got a file from her Engineering department.
And she wanted to count how many records were visible.
So, she came down here and did =COUNTA.
COUNTA because we have alpha data and she counted and said 19 items.
How about we just kind of eyeball this 4, 5, 6, 7, 8, 9, 10, 11, 12.
There's only 12 items there.
and isabelle wrote and said, "how can I get it to count just the visible items?" Well hey, take a look at this.
Someone has gone through and hidden some rows.
So, row 4 is hidden in.
Isabell does not want those included in the COUNT.
So, the trick is not to use COUNT or COUNTA.
We have to switch over to SUBTOTAL.
SUBTOTAL is the amazing function that can ignore rows that have been hidden either rows have been hidden by a Filter or rows that have been hidden using Format Row Hide.
So, hey we're going to choose COUNTA.
but we don't want the number 3 COUNTA.
The number 3 COUNTA does the visible rows, assuming that everything's been hidden by a filter we want the 103.
103 is the kind of newer version.
Although it came along 10 years ago, that says we're also going to ignore rows hidden by using the Format Row Hide.
So, we'll specify a range here A2 to A20.
And you'll see instead of 19, we get 12.
Perfect! Also if we wanted to sum over here, we would use SUBTOTAL109.
nine being the one for SUM.
Will show us just the total of the visible and let's do a quick test here.
We'll come up and do format row hide, and you see that it changes to 11.
So, great way to go using SUBTOTAL, instead of COUNT or COUNTA.
When you need to COUNT only the visible cells.
Thanks to Isabell for sending that question in.
Thanks to you for stopping by.
See you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top