Forum Discussion
Adjust a formula to ignore hidden/filtered rows of data
If you're on 365, you could use this:
Replace 'list' with your range.
=LET(visible,DROP(REDUCE("",List,LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,VSTACK(a,v)))),1),ROWS(UNIQUE(visible)))
- Hi Patrick, i know it's too late, but... the solution isn't working correctly. There is an edge case where 1st value in the "List" is also a unique value. In this case, DROP removes that unique element if it is visible resulting in incorrect count. How can this be improved? I tried to understand this "rocket science", but this is the first time i see REDUCE, LAMBDA and VSTACK, so i don't quite get it how it works...
I created a sample table in Excel called names, with the only column called Name:
Name Name1 Name2 Name3 Name2 Name4 Name3 Name5 Name2 In the subtotals row I entered this formula:
=LET(visible,DROP(REDUCE(,[Name],LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,VSTACK(a,v)))),1),ROWS(UNIQUE(visible)))It shows 1 less unique values when Name1 is among filtered values, correct number of unique values if Name1 is not selected, and #CALC! when only Name1 is selected. How can this be remediated?
g0ldfinch You've omitted the initial_value argument of the REDUCE function, which is causing the incorrect results. Input either an empty string "" or a zero 0... it can actually be anything in this case, because the DROP function will remove it from the final results anyways.
- 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))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.
- I appreciate the follow up question, I have the entire set of data set up as a table and I'm using the menu at the top to apply the filter. So I guess I am filtering right now instead of hiding, I just referenced them as hidden as the row numbers continue.
The issue I'm running into now is that the 4505 I believe is accurate, no true way to verify, so the 5138 the other formulas are giving me at the start seem a bit over inflated.
