Sum the last 52 values out of 104??

Evening All

I am trying to capture information around hours worked.

I have 104 columns of data (weeks numbered 1-104).

All Rows have numerous zeros as well as a range of figures (some weeks worked, others not).

Some Rows have less than 52 values that are greater than zero, others have more than 52.

On each Row, I need to know the Sum the most recent <52 values that are greater than Zero - is this possible?

Many Thanks

Jax

Hi,
Since you didnt upload a sample file I made a simulation with 10 variables instead of 52

The formula in D5 sums up all 9 variables that are not equal to zero.
please find attached.

=SUM(OFFSET(A3,,,,AGGREGATE(15,6,((1/(A3:S3<>0))*COLUMN(A3:S3)),10)-1))

If you want the most recent 52 non-0, right-to-left, then:

Formula:

Please Login or Register  to view this content.

XLent you are an absolute genius! Thank you so so much!