If I have a column in an excel sheet populated, and there are some hidden cells. Is there a way I can count how many cells are filled, not including the hidden ones?
2006-11-03 08:27:27 · 6 answers · asked by I scream for ICE CREAM!! 3 in Computers & Internet ➔ Software
If you have hidden the row by setting the height to zero, which is also what excel does when you pick "format", "row","hide", then you will first have to have a way to determine the row height of each row in the range. There is not a function to do this in excel (although there is one to determine width), but here is a link to a page that can tell you how to create one
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse_thread/thread/65d8576809d44e8d/a502f72888df4806%23a502f72888df4806
After you've done that, you could probably use a =DCOUNTA(range,column offset,criteria) function.
For your criteria value you would have to use something like =AND(RowHeight()>0,ISNUMBER())
You would have to play around with the syntax and exact functions depending on whether you are counting numbers or text, and remember you need to build the rowheight function before putting it in the formula.
I find it is best to build a complex formula from the inside out. For instance, start with just the isnumber formula and make sure it returns a valid result. then try the rowheight formula you've built and make sure it returns a valid row height on just a current cell, before you us it as a criteria. then put them together within the logical =AND function.
you might want to play with working with common functions within an =and before trying the =rowheight function.
2006-11-03 09:09:03 · answer #1 · answered by boonietech 5 · 0⤊ 0⤋
I am assuming that you are hiding certain rows. For example, lets say that the numbers are entered in column A. In column B, enter a 0 in cells where the row is hidden, a 1 in cells where the row is not hidden. In column C, enter the following formula in each cell: =IF(B__=1,A__,"")
The underline represents the current row number. For example, suppose you have the number 7 in cell A3. In cell B3, enter 0 if row 3 is hidden, 1 if row 3 is not hidden. In cell C3, you have the formula =IF(B3=1,A3,""). So if row 3 is hidden, cell C3 will contain a blank text value. If row 3 is not hidden, cell C3 will show 7.
Presumably you were using the COUNT function to count how many cells were filled in. So instead of using the COUNT function for the cells in column A, use it to count the cells in column C. Any cells in column A that are hidden will not be copied over to column C, and will not be included in the count.
This should work, but the only flaw is if you frequently change which rows are hidden or not hidden. You would have to change the value in column B when you hide or unhide a row.
2006-11-04 15:47:44 · answer #2 · answered by Alan S 6 · 1⤊ 0⤋
i'm assuming which you're hiding particular rows. as an occasion, shall we are saying that the numbers are entered in column A. In column B, enter a nil in cells the place the row is hidden, a a million in cells the place the row isn't hidden. In column C, enter right here formulation in each and each cellular: =IF(B__=a million,A__,"") The underline represents the present row selection. as an occasion, think you have the selection 7 in cellular A3. In cellular B3, enter 0 if row 3 is hidden, a million if row 3 isn't hidden. In cellular C3, you have the formulation =IF(B3=a million,A3,""). So if row 3 is hidden, cellular C3 will contain a sparkling text textile value. If row 3 isn't hidden, cellular C3 will tutor 7. probably you have been making use of the count selection functionality to count selection what proportion cells have been filled in. So rather of making use of the count selection functionality for the cells in column A, use it to count selection the cells in column C. Any cells in column A that are hidden will by no potential be copied over to column C, and could by no potential be secure interior the count selection. this could paintings, however the only flaw is that in case you many times replace which rows are hidden or no longer hidden. you will would desire to alter the cost in column B once you cover or unhide a row.
2016-10-03 06:10:33 · answer #3 · answered by marceau 4 · 0⤊ 0⤋
Use the help program in Excel with the count command. There should be an option that will let you include or exclude hidden cells.
2006-11-03 08:30:16 · answer #4 · answered by St N 7 · 0⤊ 0⤋
By excel count function the cells of excel can be counted.
2014-08-15 15:10:35 · answer #5 · answered by Anonymous · 0⤊ 0⤋
Write a macro to find that
2006-11-03 08:31:37 · answer #6 · answered by Anonymous · 0⤊ 1⤋