| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
How do you ignore hidden rows in a countif() function
I'd like to count the number of cells with the value "Open" that are not
hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#2
|
|||
|
|||
|
Try...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1 00="Open")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#3
|
|||
|
|||
|
Thanks for the help. I cannot get this to work. It always returns 0. Do I
need to tweek it a little? Scott "Domenic" wrote: Try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="Open")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#4
|
|||
|
|||
|
Scott,
It works fine as it is, as long as the values 'Open' are in L2:L100. You might need to extend the range. -- HTH Bob Phillips "Scott buckwalter" wrote in message ... Thanks for the help. I cannot get this to work. It always returns 0. Do I need to tweek it a little? Scott "Domenic" wrote: Try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O pen")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#5
|
|||
|
|||
|
Make sure that the second argument is preceded by a double negative
'--'... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1 00="Open")) While I included the double negative in my original formula, for some reason it's missing in the one quoted in your message. Hope this helps! In article , "Scott buckwalter" wrote: Thanks for the help. I cannot get this to work. It always returns 0. Do I need to tweek it a little? Scott "Domenic" wrote: Try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O pen")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#6
|
|||
|
|||
|
Thanks! It works! I'm interested in Why it works. Is there an explanation
somewhere? I understand the functions being used (mostly), but I dont see how putting them together in this way makes this work. Thanks. "Domenic" wrote: Make sure that the second argument is preceded by a double negative '--'... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1 00="Open")) While I included the double negative in my original formula, for some reason it's missing in the one quoted in your message. Hope this helps! In article , "Scott buckwalter" wrote: Thanks for the help. I cannot get this to work. It always returns 0. Do I need to tweek it a little? Scott "Domenic" wrote: Try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O pen")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
|
#7
|
|||
|
|||
|
If we take a look at the following formula...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1 00="Open")) ....SUBTOTAL returns an array of 1's and 0's for the range of interest. It returns 1 for all visible cells that are not empty, and returns 0 for those that are hidden. Therefore... SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)) ....evaluates to something like... 1 1 0 0 0 1 0 ....and so on for the remaining cells in the range. The second argument... --(L2:L100="Open") ....evaluates to something like... 1 0 0 1 0 0 1 ....and so on for the remaining cells in the range. Then, SUMPRODUCT multiplies the evaluations and sums the result. Hope this helps! In article , "Scott buckwalter" wrote: Thanks! It works! I'm interested in Why it works. Is there an explanation somewhere? I understand the functions being used (mostly), but I dont see how putting them together in this way makes this work. Thanks. |
|
#8
|
|||
|
|||
|
Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html
-- HTH Bob Phillips "Scott buckwalter" wrote in message ... Thanks! It works! I'm interested in Why it works. Is there an explanation somewhere? I understand the functions being used (mostly), but I don't see how putting them together in this way makes this work. Thanks. "Domenic" wrote: Make sure that the second argument is preceded by a double negative '--'... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1 00="Open")) While I included the double negative in my original formula, for some reason it's missing in the one quoted in your message. Hope this helps! In article , "Scott buckwalter" wrote: Thanks for the help. I cannot get this to work. It always returns 0. Do I need to tweek it a little? Scott "Domenic" wrote: Try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O pen")) Hope this helps! In article , "Scott buckwalter" wrote: I'd like to count the number of cells with the value "Open" that are not hidden. 1) =COUNTIF(L:L,"Open") This does not ignore hidden rows 2) =SUBTOTAL(3,L:L) This ignores hidden rows but counts everything What I like is a way to combine these two functions: 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns a #VALUE error. 2) Is there an ishidden() function? I could do this: {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0), 0))} But the ishidden() function does not exist. 3) I tried replacing the ISHIDDEN() with a CELL() function. This gets me closer, CELL("width") return 0 if the column is hidden, but not if the row is hidden, I'd need to use CELL("height"). The end result: {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100) 0,1,0),0))} Does not work since CELL("height") does not work. Thanks for your help, Scott |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Radom hidden rows | Excel Discussion (Misc queries) | |||
| how to ignore hidden cells with a countif | Excel Worksheet Functions | |||
| change excel row height without showing hidden rows | Excel Worksheet Functions | |||
| hidden rows & columns slow file open | Excel Discussion (Misc queries) | |||
| Ignore Hidden Rows in Sum Function? | Excel Discussion (Misc queries) | |||
Hybrid Mode
