Sum 52 cells worth of data within a larger range working backwards & excluding blank cells

Hello all,

I have been working on a formula to enable me to sum a total of 52 cells excluding any blank or 0 cells within a row i.e C2 to DC2.

I need to be able to use a maximum of 52 cells working back from DC2 to get the total, also allowing for rows which may only contain 6 cells worth of data (Employee 4), however every formula I have tried so far has left me with Num, Value, Ref errors along with the totally wrong total figure while using AI to help.

An example would be that the total worked out manually for Employee 3 should be 2102 after dismissing the 4 blank cells within the full range working backwards.

If the data needs to be transposed, this is also an option I can work with but since each cells refers to hours worked within a week period, I need the formula to always work from the latest cell, backwards.

I would greatly appreciate any help or advice!

Thanks!



Excel example data 52 cell query.xlsx