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