Sum 7 left cells or up to the first negative number

[10ml]

Hello,

I'd like to sum the first 7 left cells or up to the first negative number (always to a max of 7 cells)

EX. =SUM(A1:G1) to cell G2 This is pretty much easy
My problem is, if there is a negative number in these 7 cells, I have to sum up before that cell without counting it or going over it.

A1= 1
A2= 2
A3=(-2)
A5= 2
A6= 3
A7= 4
Total on Cell G3 would have to be 9
A7+A6+A5=9 since A3=(-2) I only count before the first negative number

Is there an easy way to do that?

Thank you

[zbor]

What about A4?

[10ml]

OOOps,

Just forgot to add it in

In that case, I would need to add it up until the first negative number

[martindwilson]

try
=SUM(OFFSET(A1,LOOKUP(2,1/(A1:A7<0),ROW(A1:A7)),,7-LOOKUP(2,1/(A1:A7<0),ROW(A1:A7))))

[MarvinP]

Hi 10ml,

Find the attached with a helper column and what I think you want as an answer.

[10ml]

Drivers.xls

Thank You,

I Think I gave you the wrong info

I need the total of the seven previous column of row 6 and the total on row 7
In my example, I sum up every 7 previous column but it is substracting the negative number



Hope you understand my example!

[HaHoBe]

Hi, 10ml,

according to Forum Rule #8 you should have included the link to your other thread as this thread was posted 2 minutes later.

Ciao,
Holger