Forum Discussion
Adjust a formula to ignore hidden/filtered rows of data
I have a formula already setup to calculate the total occurrences of a unique ID in a column. =SUM(IF(ISNUMBER(A2:A2719)*COUNTIF(A2:A2719,A2:A2719)=1,1,0)) But when I have a filter set on a t...
So here's where I'm at, if I show all the data and use my old formula, which I will post all the formulas I'm using below, it gives me 4505 on all data and when I hide some rows.
The LET and the other one formula gives me 5138 when it shows all and 4059 when hidden.
My concern comes in the 4505 I believe to be correct on all data, so how can I adjust to get the other formulas to 4505 on the original set of data?
Old/Original
=SUM(IF(ISNUMBER(A2:A5928)*COUNTIF(A2:A5928,A2:A5928)=1,1,0))
New
=LET(visible,DROP(REDUCE("",A2:A5928,LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,VSTACK(a,v)))),1),ROWS(UNIQUE(visible)))
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A5928)-ROW(A2),,1)), IF(A2:A5928<>"",MATCH("~"&A2:A5928,A2:A5928&"",0))),ROW(A2:A5928)-ROW(A2)+1),1))
The LET and the other one formula gives me 5138 when it shows all and 4059 when hidden.
My concern comes in the 4505 I believe to be correct on all data, so how can I adjust to get the other formulas to 4505 on the original set of data?
Old/Original
=SUM(IF(ISNUMBER(A2:A5928)*COUNTIF(A2:A5928,A2:A5928)=1,1,0))
New
=LET(visible,DROP(REDUCE("",A2:A5928,LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,VSTACK(a,v)))),1),ROWS(UNIQUE(visible)))
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A5928)-ROW(A2),,1)), IF(A2:A5928<>"",MATCH("~"&A2:A5928,A2:A5928&"",0))),ROW(A2:A5928)-ROW(A2)+1),1))
I believe the problem is with accounting for hidden rows. The closest Excel can come formula-wise to determining if a cell is part of a hidden row is the CELL function which can obtain width. A hidden row's height is 0.
I've written a Lambda in the past which can account for hidden columns but the caveat is if one show/hides columns, the formula won't update until the formula is re-calculated. A workaround might be to filter out the rows instead of hiding them so you can use one of the formulas provided in this discussion.