Stopping a formula

[Roachy]

Another one

Is there anyway I can stop a formula mid way through so for example if I was
looking to sum up the below numbers until it finds the 6 and then stops..

1
4
6
8
10

so the sum would be 11 and wouldnt continue to sum the 8 and 10 (these
fields would continually change so the 6 would not remain in the same place)

Cheers

 

[Roger Govier]

Hi

Will there be any values below 6 later in the series?
If not, then
=SUMIF(A:A,"<=6")

 

[Roachy]

Hi Roger,

Yes im afraid the values will be pretty random so the below formula wont
work is there another way around this?

Cheers for you help

 

[Brad]

Not the ideal, but here is one way
Create a helper column (my work is in a2:b8)

= 1 1
=IF(AND(B2<>6,A2=1),1,0) 4
=IF(AND(B3<>6,A3=1),1,0) 6
=IF(AND(B4<>6,A4=1),1,0) 10
=IF(AND(B5<>6,A5=1),1,0) 15
=IF(AND(B6<>6,A6=1),1,0) 6
=IF(AND(B7<>6,A7=1),1,0) 12

Then in D2 I have
=SUMPRODUCT(A2:A8,B2:B8)

Adjust as needed.

This assumes that the six is not the first number....

This could be modified as well.