Forum Discussion

lori_m's avatar
lori_m
Iron Contributor
Dec 17, 2024

Interesting scenario and method. 

I played around a little looking to see if it was possible to avoid the use of thunks by combining references into arrays and had partial success. If the names of the ranges are Table1,..,Table4 we can define a multi-area range: Tables=Table1,Table2,Table3,Table4 and try the following formula to convert this to an array of ranges. 

=SUMIF(LAMBDA(T,MAP(SEQUENCE(AREAS(T)),LAMBDA(a,DROP(INDEX(T,,,a),{0}))))(Tables),"<>")

Strangely, this appears to work fine in Monthly Enterprise Channel but not Beta Channel.

An alternative that was more successful was to build a multi-area reference via REDUCE as shown

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 24, 2024

Nice idea with areas. In general we may avoid explicit markers and don't care about tables location. If only to keep existing layout for each table.

=LET(
  Tables,    (Table1,Table2,Table3,Table4),
  n,         SEQUENCE( AREAS(Tables) ),
  bar,       1000000,
  city,      LAMBDA(i, OFFSET(  TAKE(INDEX(Tables,,,i),1,1),-2,  ) ),
  qty,       LAMBDA(i, COUNTIF( DROP(INDEX(Tables,,,i), ,1), ">" & bar ) ),
  VSTACK(
    {"Countries","Cities > 1m"},
    HSTACK(
       MAP( n, city ),
       MAP( n, qty  )
    )
  )
)

and have not thousands of tables to care about performance.

Resources