I have a small worksheet that I gather information from using sumifs. The worksheet has 100 rows and 20 columns.
I want to exclude hidden rows from my calculations, the rows that are hidden will vary over time.
I would like to determine a way to identify and mark hidden rows, so that I can I can use the mark as a criteria to exclude those rows in my sumifs.
Hi Cbird- You could add a (hidden?) helper column with this formula in row 2 and copied down. It shows 1 if the row is visible, 0 if it's hidden (or A is blank).:Now just use the helper as an additional criteria for your COUNTIFS. For example, assuming Z is the helper column, this counts VISIBLE instances of "N" in column B:
Please Login or Register to view this content.And here's a link to a similar thread, with other solutions. https://www.excelforum.com/excel-for...ml#post4788301Please Login or Register to view this content.
Last edited by leelnich; 11-28-2017 at 05:22 PM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Thanks that makes sense.
Any thoughts on why =SUBTOTAL(103,A2) in my worksheet returns a 0 instead of 1 on a visible row?
Is there a value in column A for that row? SUBTOTAL(103..) mimics COUNTA, so it returns 0 if A is blank. Also, make sure calculation is set to automatic.
Last edited by leelnich; 11-28-2017 at 11:50 PM.
Thank you!
That was my issue.
Works perfectly now.
Excellent, glad I could help.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks