Sum of rightmost number in column where numbers are separated by underscore?

Question

So I have a list of workers, one name per row, and each column has a date in the header. Against each worker under each date is a start time and number of hours worked in the format 6_8 if they start at 6 and work 8 hours, 10_4 if they start at 10 and work 4 hours etc. The cell is blank if they did not work.

At the bottom of this table I'd like to be able to extract the total number of hours worked on a particular day. Is this possible?

I understand it would make more sense to split the cells, but is my above problem solvable without using vba?

Answer 1

Your result is zero because you are extracting text representations of numbers, and not real numbers. In the formula below, note that I preceded the MID with a double unary (--) which will convert the value to a number. Since the formula is an array formula, we must enter it by holding down ctrl+shift while hitting enter

=SUM(IFERROR(--MID(B$2:B$7,FIND("_",B$2:B$7)+1,99),0))

Answer 2

=SUMPRODUCT(IF(ISERROR(FIND("_",B2:B5,1)),0,RIGHT(B2:B5,LEN(B2:B5)-FIND("_",B2:B5,1)))*1)
This Formula is array Formula press Ctrl +Shift + Enter after writing it under each column you want to sum containing _
I used in column B your Data if you have more rows change B2:B5 to reference your column
the Iserror is used to check for empty cells and avoid #Value in the result
It checks the length of the Cell and the place of _ to count the number of digits on the right of _