Forum Discussion

BCowans's avatar
BCowans
Copper Contributor
Sep 05, 2023

Formula help please

Hello,  I need help with a formula but I'm unsure if it's possible to do so.  It will include a few different tabs.  Tab one, I need the formula to pull the total percentage based off another tab....
Formulas and Functions
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Sep 05, 2023

BCowans 

Something of an effort to bypass Microsoft's array of array specification error by using MAP over a 2D array.

= LAMBDA(hdr,clr,table,
    LET(
      tbleHdr,   TAKE(table, 1,-2),
      tbleBody,  DROP(table, 1, 1),
      tbleColor, DROP(table, 1,-2),
      mask,      ISTEXT(hdr)*ISTEXT(clr),
      hdrArr,    IF(mask, hdr),
      clrArr,    IF(mask, clr),
      MAP(hdrArr, clrArr, LAMBDA(h,c, COUNTIFS(XLOOKUP(h, tbleHdr, tbleBody), "Y", tbleColor, c)))
    )
  )(header, color, Table1[#All])

 

Resources