How to total sum of non-numerical values in a cell

Brass Contributor


how can I take the values from the table on the right and total them up at the bottom of the weekly tally on the left?


So say there is a weekend (worth 12 hours), four "day" shifts (worth 8 hours each) and two off shifts (worth 0 hours each) and get the total sum at the bottom of each week, so for this one it will be 44?

5 Replies



Perhaps this:




Wow thanks for the super quick response. That works for the one row, but if I copy it down it doesn't work for the other rows totals, like this




how can I get all the other "sum" rows to work with the same table on the right?


Lock in the ranges:






It still comes up with #N/A, am I doing something wrong?

I didn't realize there were some blanks in the range that would cause an error with the XLOOKUP. I've revised my previous post.