Forum Discussion

mplanner's avatar
mplanner
Copper Contributor
Nov 02, 2021

summing text converted to numbers

Hello,   I am in the process of setting up an attendance "tracker" and need some direction with a formula.   I need to convert text codes into numerical values and then sum them together.    Th...
excel
Formulas and Functions
office 365
PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Nov 03, 2021

mplanner 

For anyone interested in programming the new Lambda function, I built a function that collects the row totals for every month and staff member as a single dynamic array.

"AggregatedValueλ"
= LAMBDA(m,s,
      LET(
         staffCodes,  CHOOSE(s, SW!code, RW!code),
         monthCodes,  INDEX(staffCodes, 2*m-1,),
         monthValues, SWITCH(monthCodes, "T",1,"LE",1,"EA",2,"UA",3,0),
         SUM(monthValues))
   )

with a final worksheet formula of the form

= MAKEARRAY(12,2,AggregatedValueλ)

Resources