Backwards determination for non-blank cell address in a row?

[cybern8]

I have a spreadsheet in the following form:

Please Login or Register  to view this content.

I'd like to sum Row2/Cols2~4 based on a) Row1/Col4 having a value and b) the first preceding non-blank column in Row1 is Col1. Thus, the Result for Col4 would be 90 (40+30+20).

I believe I need a function to determine the cell address of the previous non-blank cell in Row1 in order to sum Row2 from that endpoint.

Any suggestions for how do to this?

[cybern8]

I have data in a row as follows:

Please Login or Register  to view this content.

Starting at Row1/ColD, how can I search left-ward to find the address of the first non-blank value in Row1 (i.e., Row1/ColA)?

Thanks.

[olasa]

This is an array formula which means that you have to Type the formula. Then hold down Ctrl+Shift and hit Enter, just hit enter will not work.
The 0 values are hidden with conditional formatting (Menu:Format, Conditional Formatting...)
It's not the most beautiful formula, but it works.

Hope it helped
Ola Sandström

[olasa]

See the other reply + attached file.
//Ola

[cybern8]

Olasa, your solution is brilliant! Thanks!

[olasa]

Thanks, I'm glad to hear it was appreciated.
//Ola