Converting text into numbers for sum on a roster

booragal 

Since you are a 365 user, I have added 3 further solutions.  The first 'Arr' uses MMULT to evaluate the daily manning levels for the month as a single calculation.  The second 'LET' uses the LET function to provide additional local variable to make the calculation more meaningful.  The final one 'LAM' uses functionality that is still on beta release.  The LAMBDA function will allow the user to define a formula in terms of dummy variables and later provide the actual variables as parameters, thus allowing the user to define their own functions.

Somewhere along the line, you will reach the point where your reaction is OMG, let me out of here!  I still believe there is no harm in knowing that other solution strategies are possible.

Arr

= MMULT(
    SIGN(TRANSPOSE(fte)), 
    XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0)
  )

LET

= LET(
  rowFilter,SIGN(TRANSPOSE(fte)),
  shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0),
  MMULT(rowFilter,shiftValues) )

LAM

= LET(
  shiftValues,XLOOKUP(ShiftData,ShiftHours[Code],ShiftHours[Value],0),
  FILTEREDTOTAL(shiftValues,SIGN(fte)))

where FILTEREDTOTAL is defined to be

= LAMBDA(Array,filter,
     MMULT(TRANSPOSE(filter),Array)
  )

Have fun.