summing text converted to numbers

[mplanner]

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. 

The best I have been able to figure is the formula below (located on I29 for the moment)

=SUMIF(C4:AG4,IFS("T",1,"LE",1,"EA",2,"UA","3"),C4:AG4)

This only returns a value of 0.

using office 365

Any help would be appreciated.

Thanks!

[#2]

@mplanner 

=COUNTIF(C4:AG26,A36)*E36+COUNTIF(C4:AG26,A35)*E35+COUNTIF(C4:AG26,A34)*E34+COUNTIF(C4:AG26,A33)*E33

=SUM(COUNTIF(C4:AG26,A36)*E36,COUNTIF(C4:AG26,A35)*E35,COUNTIF(C4:AG26,A34)*E34,COUNTIF(C4:AG26,A33)*E33)

Is this the formula you are looking for?

[#3]

@mplanner As a variant:

=SUM(XLOOKUP(C4:AG4,A30:A36,E30:F36,0))

[#4]

@mplanner 

A number of strategies with 365

= SUM(SWITCH(JANURARY,"T",1,"LE",1,"EA",2,"UA",3,0))

= SUM(XLOOKUP(JANURARY, codeList, codeValue, 0))

= SUM((JANURARY={"T";"LE";"EA";"UA"})*{1;1;2;3})

The problem with the original formula was that the IFS function did not reference the data to be tested.

An interesting challenge with 365 would be to produce a single summary table, as a dynamic array, showing the totals for each staff member for each month.

p.s.  Please excuse the defined names.  Having described direct cell referencing as an abomination that has no place in any computational process, I tend to avoid their use.  I defined 'JANURARY to be a sheet local name so that it could be used on each sheet with the desired result.

[#5]

@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λ)