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

       

       

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

        g0ldfinch You seem to have a pretty good handle on the logic used with the REDUCE / VSTACK method. A more appropriate way to avoid the #CALC! error, though, with the UNIQUE / ROWS combination is to wrap it in the IFERROR function to return 0.

         

        Personally, I wouldn't use REDUCE / VSTACK in this situation, because there are a number of other options that work well and perform better. SergeiBaklan already provided one such method using the MAP function with TOCOL. Another option is to simply use the FILTER function with BYROW in the include argument, as follows:

         

        =IFERROR(ROWS(UNIQUE(FILTER([Name], BYROW([Name], LAMBDA(r, SUBTOTAL(3, r)))))), 0)

         

        The SUBTOTAL function will return 1 for visible rows and 0 for hidden/blank rows, which are interpreted by the FILTER function as TRUE and FALSE. Cheers!

Resources