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
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
    That didn't seem to work
  • 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)))