how to count only visible cells

[Benjaminmin]

Hi,

I have a spreadsheet with a lot of hidden rows in.

How can I do a count that only counts information in the visible cells?

Thank you very much in advance
Ben

 

[crook_101]

Hi Benjamin

I do a lot of work that includes dupes...bane of my life!!

A simple formula as shown below, changed to your range will save you manually hiding duplicated rows!!

Excel Workbook
	A	B
1	header	Dupe Check
2	Adefrt	dupe
3	Adefrt	dupe
4	Adefrt	dupe
5	Adefrt	dupe
6	Adefrt	dupe
7	Adefrt	dupe
8	Adefrt	notdupe
9	Bdsaojd	dupe
10	Bdsaojd	dupe
11	Bdsaojd	dupe
12	Bdsaojd	dupe
13	Bdsaojd	dupe
14	Bdsaojd	dupe
15	Bdsaojd	dupe
16	Bdsaojd	notdupe
17	Cwsefq	dupe
18	Cwsefq	dupe
19	Cwsefq	dupe
20	Cwsefq	dupe
21	Cwsefq	dupe
22	Cwsefq	notdupe
23	Dser	notdupe
24	fsrt	notdupe
25	ysrfqe	notdupe
Sheet1

Filter on the added column for "Notdupe" and this will give you the total!!

HTH

Regards,
Ian

 

[netarc]

do u need a calc, or just a quick way to effect the count? You can do the latter by going to the top cell, then hit CTRL-SHIFT-<down arrow>, which will highlight all contiguous cells from the top and display the "count" in the lower right hand corner (excel 2007+)

 

[Jonmo1]

See post #7

 

[crook_101]

OP stated that he is using 2003 at work

 

[crook_101]

Yup, post #7 will work, just trying to save the OP having to manually hide 7K+ rows manually if he needs to repeat the task

 

[crook_101]

To your solution, not a jot....my reference to 2003 was in response to netarc, we just cross posted:

do u need a calc, or just a quick way to effect the count? You can do the latter by going to the top cell, then hit CTRL-SHIFT-<DOWN arrow>, which will highlight all contiguous cells from the top and display the "count" in the lower right hand corner (excel 2007+)

Ian

 

[Jonmo1]

To your solution, not a jot....my reference to 2003 was in response to netarc, we just cross posted:



Ian

got it, that's why I deleted my last post.....

 

[MrVillareal]

Hi,

If Hidden try, ACTIVE CELL IN B1

=SUBTOTAL(102,A1:A100) OR
=AGGREGATE(2,5,A1:A100)

 

[Benjaminmin]

Ian's got the right idea, but not quite implimented correctly...

The option 9 in subtotal = SUM
so it was ADDING all the values, not COUNTING them..

If you want a count, it should be option 2

Also, it only ignores filtered rows, not rows hidden by hand...
Add 10 to the 2 (i.e. 102) to make it also ignore manually hidden rows..

Try
=SUBTOTAL(102,A1:A7751)

Also, 102 = count of NUMBERS
You may want 103 = count of ANY value, number OR text


Hope that helps.

Hi guys,

Sorry for being late in answering (different time zone).

Thank you very much for all your efforts in helping me with this.

The Subtotal(102) indeed did the trick perfectly for me, so thank you for that Jonmo.

Also big thanks to crook_101 for your many posts and patience! -And of course thanks to anyone else who posted.

This is the best forum ever!

Cheers

 

[kotahitanga]

Hmm that still returns 7610 (which is the number of cells that has content in them), so it doesnt ignore the hidden cells.

I think the solution is already mentioned... did you try this using 103



=SUBTOTAL(102,A1:A7751)

Also, 102 = count of NUMBERS
You may want 103 = count of ANY value, number OR text