count function for spreadsheet with hidden rows

[illinifan1]

I have a spreadsheet where I have hid the rows that have irrelevant data to my testing in them (i do not want to delete them to keep the integrity of the data initially provided to me from my IT Department)

Anyway, I may have 250 rows hidden from a set of data with 400 total rows, is there a function I can use that will tell me how many rows have data in them?

I can't just take row 400 minus my header row at 6 because of the hidden rows and there are up to a few thousand rows on some of my spreadsheets.

Thanks!

 

[Rick Rothstein]

I have a spreadsheet where I have hid the rows that have irrelevant data to my testing in them (i do not want to delete them to keep the integrity of the data initially provided to me from my IT Department)

Anyway, I may have 250 rows hidden from a set of data with 400 total rows, is there a function I can use that will tell me how many rows have data in them?

I can't just take row 400 minus my header row at 6 because of the hidden rows and there are up to a few thousand rows on some of my spreadsheets.

Thanks!

Look up the help on SUBTOTAL... it has options for doing counts, sums, averages, etc. where it can include or ignore hidden rows. For example, to count the numerical values in the visible cells in the range A1:A400...

=SUBTOTAL(102,A1:A400)

 

[illinifan1]

what if i don't have numerical values? this is all regarding internet usage/email data?

 

[Rick Rothstein]

what if i don't have numerical values? this is all regarding internet usage/email data?

You did not lookup SUBTOTAL in the help files as I suggested in my first post, did you? Everything about how to use this function is covered in there. If you are going to be doing work on sheets with hidden rows or columns, you should get to know this function intimately. As for your direct situation, more than likely, you would want the COUNTA option instead then. Try changing the 102 to 103.

 

[illinifan1]

i went to excel help - it was telling me how to put columns on the left hand side of my data with little plusses to to totals, grand totals, etc. I'll look again - it just didn't help me too much at first glance. Thanks

 

[illinifan1]

subtotal(103, A1:A400) appears to have worked - thank you