Forum Discussion

mtarler's avatar
mtarler
Silver Contributor
Jun 28, 2022

Patrick2788  without those new beta functions it seems harder (at least to me) so I added the sheet names and made a 2 level BYROWS() call:

=BYROW(staff,LAMBDA(e,
   SUM(BYROW(SheetNames, LAMBDA(mm, 
      LET(sm, INDIRECT(mm&"!a3:g20"), 
         swd, INDIRECT(mm&"!b3:f20"),
         smb, INDIRECT(mm&"!a4:g21"),
         smc,INDIRECT(mm&"!a5:g22"),
         1.5*COUNTIF(sm,e)-0.5*COUNTIF(swd,e)
            +SUM(COUNTIFS(sm,holidays,smb,e)+COUNTIFS(sm,holidays,smc,e)) 
         )
      )))
   ))*8*rate
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jun 28, 2022

    It's certainly a good solution with Beta functions not available. Use of INDIRECT is not ideal but the workbook is not large enough where there might be a calculation crunch.

    • mtarler's avatar
      mtarler
      Silver Contributor
      Jun 28, 2022

      Patrick2788  I agree using INDIRECT() is not ideal and I tried to avoid it but couldn't find anything that I could get to work on the 3d (i.e. multi-sheet) reference range.  I have used CONCAT/TEXTJOIN type of functions on 3d but for example using TEXTJOIN with "," delaminated created a string >1500 long so the 'traditional' trick of replacing with spaces and then using MID() to then break it up errors out due to the length.  That said I came up with a cute variant that seems to work:

      =LAMBDA(ref,
    LET(
        mm, "," & TEXTJOIN(",", FALSE, ref) & ",",
        ll, LEN(mm),
        cc, ll - LEN(SUBSTITUTE(mm, ",", "")) - 1,
        s, SEQUENCE(cc),
        sp, REPT(" ", ll),
        dd, TRIM(
            MID(
                SUBSTITUTE(
                    LEFT(SUBSTITUTE(mm, ",", sp, s + 1), ll),
                    ",",
                    sp,
                    s
                ),
                ll,
                ll
            )
        ),
        dd
    )
)

       I then wrapped that with a calculation:

      =LET(mm, ToCol(AllMonthsRef),
     holidayloc,XMATCH(holidays,--mm,0,1),
     BYROW(staff,LAMBDA(n,LET(
             ws,SEQUENCE(ROWS(mm)/7,1,1,7),
             alldays,SUM(--(mm=n)),
             wkends,0.5*SUM(--(INDEX(mm,ws)=n),--(INDEX(mm,ws+6)=n)),
             holly,SUM(--(INDEX(mm,holidayloc+7)=n),--(INDEX(mm,holidayloc+14)=n)),
             alldays+wkends+holly)))
     *8*rate)

Share

Resources