How to skip Hidden Column cell value count
Hello Friends,
I have few column values to count. I don’t want the hidden column value. In that formula the hidden value also counted. Please help me to ignore that hidden column value count, using macro/formula.
eg:
col A - col B - col C - col D - col E - col F
Task -- ok1 -- ok2 -- no1 --- ok3 -- ok4
Col D should be hidden. Final count result should be: OK = 4; No=0
File attached for more clarification. No Count Hidden.xlsx
Subtotal doesn't work, I am not sure of another way aside from making your data vertical or using vba myself.
edit:
From help it seems: The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.
Last edited by Hawkeye16; 07-15-2014 at 03:36 AM.
Despite the high cost of living, it remains very popular.
Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!
In B1 Cell
=CELL("width",B1)
Drag it upto F1 Cell
In G1 Cell
=COUNTIFS($B3:$F3,G$2,$B$1:$F$1,">0")
Drag it down and right...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
thanks Hawkeye16
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks