Sum of rows and columns after stripping non-numeric characters

[alpkhan]

Hello, I have an attendance sheet for every month where I have people on the first column and dates as headers. With each cell there could be three different variation. An empty cell with no data, two or three letter abbreviated word explaining the reason of absence, or one to two digit number (whole or decimal) followed by a letter to three. I want to calculate the sum of the row as well as the column as shown in the first picture. I tried using nested SUBSITITUE() to cover all the possible characters, but I had a problem at one point and ended up using SUBSTITUE() with INDEX() referencing a table. Just wanted to make it more flexible for future. But, with this formula I am having #VALUE errors. I marker the characters that are causing this error with red and pink. Is there a better way for me to do this? What am I doing wrong with the current method I have? (Using Excel 2016)

Reference table for INDEX(). Red cells are problematic.

What I have so far. Pink cells are causing the #VALUE error.

 

[alpkhan]

Formula:

{=SUM(VALUE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

$F24:$AJ24,

INDEX(tSubstitute[Old],1), INDEX(tSubstitute[New],1)),

INDEX(tSubstitute[Old],2), INDEX(tSubstitute[New],2)),

INDEX(tSubstitute[Old],3), INDEX(tSubstitute[New],3)),

INDEX(tSubstitute[Old],4), INDEX(tSubstitute[New],4)),

INDEX(tSubstitute[Old],5), INDEX(tSubstitute[New],5)),

INDEX(tSubstitute[Old],6), INDEX(tSubstitute[New],6)),

INDEX(tSubstitute[Old],7), INDEX(tSubstitute[New],7)),

INDEX(tSubstitute[Old],8), INDEX(tSubstitute[New],8)),

INDEX(tSubstitute[Old],9), INDEX(tSubstitute[New],9)),

INDEX(tSubstitute[Old],10), INDEX(tSubstitute[New],10)),

INDEX(tSubstitute[Old],11), INDEX(tSubstitute[New],11)),

INDEX(tSubstitute[Old],12), INDEX(tSubstitute[New],12)),

INDEX(tSubstitute[Old],13), INDEX(tSubstitute[New],13)),

INDEX(tSubstitute[Old],14), INDEX(tSubstitute[New],14)),

INDEX(tSubstitute[Old],15), INDEX(tSubstitute[New],15)),

INDEX(tSubstitute[Old],16), INDEX(tSubstitute[New],16)),

INDEX(tSubstitute[Old],17), INDEX(tSubstitute[New],17)),

INDEX(tSubstitute[Old],18), INDEX(tSubstitute[New],18)),

INDEX(tSubstitute[Old],19), INDEX(tSubstitute[New],19)),

INDEX(tSubstitute[Old],20), INDEX(tSubstitute[New],20)),

INDEX(tSubstitute[Old],21), INDEX(tSubstitute[New],21)),

INDEX(tSubstitute[Old],22), INDEX(tSubstitute[New],22)),

INDEX(tSubstitute[Old],23), INDEX(tSubstitute[New],23)),

INDEX(tSubstitute[Old],24), INDEX(tSubstitute[New],24)),

INDEX(tSubstitute[Old],25), INDEX(tSubstitute[New],25)),

INDEX(tSubstitute[Old],26), INDEX(tSubstitute[New],26)),

INDEX(tSubstitute[Old],27), INDEX(tSubstitute[New],27)),

INDEX(tSubstitute[Old],28), INDEX(tSubstitute[New],28)),

INDEX(tSubstitute[Old],29), INDEX(tSubstitute[New],29)),

INDEX(tSubstitute[Old],30), INDEX(tSubstitute[New],30))))}