Forum Discussion
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...
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.