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,


If your fidden cells are a reqult of auto filters:

=SUBTOTAL(9,A1:A100)

Adjust for your range

Regards,
Ian

 

[Benjaminmin]

Hi,


If your fidden cells are a reqult of auto filters:

=SUBTOTAL(2,A1:A100)

Adjust for your range

Regards,
Ian

Hi Ian,

Thank you for the quick response, unfortunately the hidden cells are not a result of an auto filter, but rather manually hidden, so the proposed formula returns an incorrect result for me

 

[crook_101]

Hi again,

If you are using 2007 and above, it will still work on hidden cells..


Apologies for my first post, it was full of typos....

Correct formula:

=SUBTOTAL(9,B2:B9)

Adjust for your range...

Ian

 

[Benjaminmin]

Hi again,

If you are using 2007 and above, it will still work on hidden cells..


Apologies for my first post, it was full of typos....

Correct formula:

=SUBTOTAL(9,B2:B9)

Adjust for your range...

Ian

Hi Ian,

Unfortunately I am at work, where theres nothing but good ol' 2003.

I used =SUBTOTAL(9,A1:A7751), which returned 29646656, seems a bit high for a range that only goes to 7751 Any idea what could be causing the error?

Thank you for your help.

 

[crook_101]

Are you trying doing a "SUM" or "COUNT"?/

 

[Jonmo1]

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.

 

[Benjaminmin]

Are you trying doing a "SUM" or "COUNT"?/

I am trying to do a count.

Basically there were originally around 7750 values, but I have now hidden all the values that didnt have a duplicate entry, leaving a few hundred I would guess. I am trying to see exactly how many it is that remain visible.

Appreciate you taking time to help me

 

[crook_101]

No worries, the following details what the subtotal functions do:

<TABLE style="BORDER-COLLAPSE: collapse" id=table3 border=1 cellSpacing=0 borderColor=#c0c0c0 cellPadding=0 width="80%" height=315><TBODY><TR vAlign=top><TD height=14>1</TD><TD height=14>AVERAGE</TD></TR><TR vAlign=top><TD height=14>2</TD><TD height=14>COUNT</TD></TR><TR vAlign=top><TD height=14>3</TD><TD height=14>COUNTA</TD></TR><TR vAlign=top><TD height=14>4</TD><TD height=14>MAX</TD></TR><TR vAlign=top><TD height=14>5</TD><TD height=14>MIN</TD></TR><TR vAlign=top><TD height=14>6</TD><TD height=14>PRODUCT</TD></TR><TR vAlign=top><TD height=13>7</TD><TD height=13>STDEV</TD></TR><TR vAlign=top><TD height=14>8</TD><TD height=14>STDEVP</TD></TR><TR vAlign=top><TD height=14>9</TD><TD height=14>SUM</TD></TR><TR vAlign=top><TD height=14>10</TD><TD height=14>VAR</TD></TR><TR vAlign=top><TD height=14>11</TD><TD height=14>VARP</TD></TR></TBODY></TABLE>

If you change the "9" to a "2", it will count, mea culpa....tired long day!!

Regards,
Ian

 

[Benjaminmin]

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