Help with sum function

[Shweta]

Hi All,

Please help me out on the below query

W5	W4	W3	W2	W1	W0	Result
28	L	30	L	22	0	80
8	7	9	11	7	0	27

<colgroup><col span="6"><col></colgroup><tbody>
</tbody>

In the result column, I want the sum for the last 4 weeks. If "L" is written in a column then formula should take the value from the next column but the count of the columns should not exceed 4.

For example in first case 0+22+30+28 = 80
in second case 0+7+11+9 = 27.

Please provide me an appropriate formula for this.

Thanks in advance!

Regards,
Shweta

 

[Andrew Poulsom]

For the sample data:

=SUM(OFFSET(F2,0,0,1,-(4+COUNTIF(A2:F2,"L"))))

 

[Ron Coderre]

If there may be less than 4 numbers...try this regular formula:

G2: =SUM(F2:INDEX(2:2,INDEX(LARGE((A2:F2<"")*COLUMN(A2:F2),MIN(COUNT(A2:F2),4)),0)))

Copy that formula down as far as you need.

Is that something you can work with?

 

[Shweta]

Thanks Andrew!

This formula is working perfectly.

 

[Shweta]

Thanks Ron!

This one is also working fine.