Forum Discussion

kthersh's avatar
kthersh
Copper Contributor
Feb 14, 2023

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...
Formulas and Functions
Patrick2788's avatar
Patrick2788
Silver Contributor
Feb 14, 2023

kthersh 

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)))

 

g0ldfinch's avatar
g0ldfinch
Copper Contributor
Nov 29, 2023
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...
  • g0ldfinch's avatar
    g0ldfinch
    Copper Contributor
    Nov 29, 2023

    Patrick2788 

    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?

     

    • djclements's avatar
      djclements
      Silver Contributor
      Nov 29, 2023

      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.

      • g0ldfinch's avatar
        g0ldfinch
        Copper Contributor
        Nov 29, 2023

        djclements

        Thanks for your quick response. I don't know how that happened - i compared the original formula with and without the initial value, and the result was the same... Probably, because i didn't see visible difference, i removed the initial value of the REDUCE function, and then started noticing that issue. Thanks! So.. How does that formula work? Is my understanding correct?

        1. Variable 'visible' is assigned its value, the result of the DROP function
          1. DROP removes 1st value from the result of the REDUCE function (which, in this case, must return an array of a sort)
            1. REDUCE has an initial value "" which becomes the 1st element of an array (range) 'a'
            2. Range to be reduced is the [Name] column of the table
            3. Lambda is the actual reduction formula:
              1. If a cell is hidden, that is, COUNT function returns 0 for the current array element 'v', unchanged result of the REDUCE function is returned (it contains "" during the very first iteration)
              2. Otherwise, 'v' is appended to the end of the result variable 'a'
        2. ROWS function is applied to the result of the UNIQUE function which returns a range of distinct values from 'visible'.
        3. Limitation of this formula: if there are no visible rows, we get #CALC! because Excel tries to apply UNIQUE and then ROWS to an empty array.

        How can this formula be improved to show 0 when there are no visible rows?

        ...

        I think i found the solution. It should be faster as well. We don't need to use DROP because, if there are no visible elements, we are deleting the only array element (initial value ""), and Excel returns an error. However, ROWS(DROP(array, 1)) is, in essence, the same as ROWS(array) - 1, which leads to this formula:

         

         

        =LET(visible,
     UNIQUE(REDUCE("",
                  [Name],
                  LAMBDA(a,
                         v,
                         IF(SUBTOTAL(103,v)=0,
                            a,
                            VSTACK(a,v)
                         )
                  )
            )
     ),
     ROWS(visible)-1)

         

         

Resources