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 16, 2023
You could try using the table's structured reference in the formula instead of the range. My formula is checking to see if each value in in the column is visible by using SUBTOTAL. If visible, the formula creates a stack of the IDs, removes dupes, and then returns the ROWS of the unique ID stack.
kthersh's avatar
kthersh
Copper Contributor
Feb 16, 2023
Sorry if it's a dumb question, but how do I reference the "Table's structured reference" in the formula?
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Feb 16, 2023
    While in the formula, select the table's column from the first value to the end. Don't include the header row in the selection.

    The syntax is: Table[ColumnName]
    • kthersh's avatar
      kthersh
      Copper Contributor
      Feb 16, 2023
      So:

      =SUM(IF(ISNUMBER(Table[A])*COUNTIF(Table[A],Table[A])=1,1,0))

      New
      =LET(visible,DROP(REDUCE("",Table[A],LAMBDA(a,v,IF(SUBTOTAL(3,v)=0,a,VSTACK(a,v)))),1),ROWS(UNIQUE(visible)))
      • kthersh's avatar
        kthersh
        Copper Contributor
        Feb 16, 2023
        That didn't seem to work

Resources