Counting in sheets with hidden rows

Resolved · High Priority · Version 2013

Tue 5 Sep 2017, 10:29 Delegate Stephen said...

Stephen has attended:
Excel Advanced course
Office 365 End User course

Counting in sheets with hidden rows

Please can you tell me how to:

1)use a formula to count the number of cells in a column which has hidden rows.
2)use a formula to count the number of rows in a sheet with hidden rows.
2)copy Sheet 1 with hidden rows to Sheet 2 such that Sheet 2 only displays the unhidden data.

Thanks

Tue 5 Sep 2017, 15:36 Trainer Sarah said...

RE: Counting in sheets with hidden rows

Hi Stephen

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, and more and will automatically ignore items that are not visible. When working with hidden rows use the COUNT option 103:

eg =SUBTOTAL(103,A1:A20)

To display the hidden data on a second sheet, right click on the tab of Sheet 1 and select Move or Copy... Tick Create a Copy and Ok to create Sheet 2. Select all cells in Sheet 2 using the Select All button in the top left corner (between A and 1) then right click on the headers and select Unhide to show all your hidden data.

Let me know if you have any more issues.

Kind Regards,
Sarah
Excel Trainer