Stephen has attended:
UoL 365 Digital Champions Training 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
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
