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
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Dec 03, 2023

kthersh 

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."

 

 

 

 

Resources