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 table column to ignore/hide certain data, the formula does not recalculate based on the visible rows. How can I adjust the formula so it would ignore or omit the hidden rows to calculate only off of the visible/searched data?
32 Replies
Similar to others, but my first step is to test data for visibility.
= LET( visible?, MAP(range, CountVisibleλ), visible? ) CountVisibleλ = LAMBDA(cell, SUBTOTAL(102, cell))Then actually filter data, removing unwanted values, rather than hiding data
= LET( visible?, MAP(range, CountVisibleλ), filtered, FILTER(range, visible?), filtered )A simple change then returns the count of distinct values remaining
= LET( visible?, MAP(range, CountVisibleλ), filtered, FILTER(range, visible?), COUNT(UNIQUE(filtered)) )To make the range dynamic it should be a Table (as already discussed) so
range = Table1 "for a single field Table, or" range = Table1[data] "where Table has multiple fields."kthersh I Solved a similar problem by adding a new column, named Show. And use of the Aggregate function. I Think that is the english name for it.
Agregate(3;1;[@Text]). Then the rows ahowing is 1. The hidden ones is 0.
I add a smal file as an excample. Which use the Filter function to show only rows ahowing in the original table.
/Geir
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?
- 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.
If you don't have Microsoft 365 or Office 2021, but an older version, confirm the following formula by pressing Ctrl+Shift+Enter:
=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A2719)-ROW(A2),,1)), IF(A2:A2719<>"",MATCH("~"&A2:A2719,A2:A2719&"",0))),ROW(A2:A2719)-ROW(A2)+1),1))
- Thank you for the fast response, I'm going to use a different cell range for my test, which I updated in the formula below from the copy of yours, but the calculation does not seem to correct
=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))
If I use the previous formula, I get 4505, if I use your new one I get 5138 on the same set of data=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,1))
Do you want to count the IDs that appear only one time in your data instead of counting the number of unique IDs? I've added "=1" to the solution by HansVogelaar and it returns the expected result in my sample sheet. Does this formula return 4505 in your sheet?
