Sum Consecutive negative numbers upto 4 years

[Vishal Srivastava]

Hello Experts

Looking for your help!

Here is my problem:

-20

-30

-40

50

60

-70

-80

-90

-20

-50

I want to SUM consecutive negative numbers upto 4 consecutive cells in next row.
If positive comes then it should skip the sequence and checks for the next sequence like as below:

-20

-50

-90

50

60

-70

-150

-240

-260

-50

please help.

Waiting for your solution.

Thanks in advance.

 

[XOR LX]

Hi,

Assuming your values are in A1:J1:

=IF(A1<0,SUM(A1:INDEX($A1:$J1,COUNT($A1:A1)-IFERROR(1/(1/(MOD(COUNTIF(A1:INDEX($A1:$J1,IFERROR(MATCH(1,0/($A1:A1>=0)),1)),"<0")-1,4))),0))),A1)

and copied to the right.

Note that this formula may require committing with CTRL+SHIFT+ENTER, depending on your version of Excel.

Regards

 

[p45cal]

I can get this:

In D3 the formula:

=CHOOSE(MAX(1,INDEX(FREQUENCY(IF(A2:D2<0,COLUMN(A2:D2)),IF(A2:D2>=0,COLUMN(A2:D2))),COUNTA(FREQUENCY(IF(A2:D2<0,COLUMN(A2:D2)),IF(A2:D2>=0,COLUMN(A2:D2)))))),D2,SUM(C2:D2),SUM(B2:D2),SUM(A2:D2))

which, depending on your version of Excel may need to be committed to the sheet with Ctrl+Shift+Enter rather than just Enter. Then copy to the right.
Cells B3 and C3 have variants of the same formula.
Cell A3 formula is ultra simple.
The last cell highlighted in yellow is my only concern; once you've found a sequence of 4 negatives you seem to start again - I gave up at that point!

 

[XOR LX]

We could also employ OFFSET, which often gets a hard press for being volatile (perhaps justifiably), though is a very useful and flexible function:

=IF(A1<0,SUM(OFFSET(A1,,,,-TEXT(MOD(COUNT($A1:A1)-IFERROR(MATCH(1,0/($A1:A1>=0)),0),4),"[=0]\4;0"))),A1)

though here I am abusing the unintended use of a negative number for that function's width parameter.

Regards

 

[Vishal Srivastava]

Hi,

Assuming your values are in A1:J1:

=IF(A1<0,SUM(A1:INDEX($A1:$J1,COUNT($A1:A1)-IFERROR(1/(1/(MOD(COUNTIF(A1:INDEX($A1:$J1,IFERROR(MATCH(1,0/($A1:A1>=0)),1)),"<0")-1,4))),0))),A1)

and copied to the right.

Note that this formula may require committing with CTRL+SHIFT+ENTER, depending on your version of Excel.

Regards

Wow! That's works!

Thanks XOR LX!!

 

[Vishal Srivastava]

We could also employ OFFSET, which often gets a hard press for being volatile (perhaps justifiably), though is a very useful and flexible function:

=IF(A1<0,SUM(OFFSET(A1,,,,-TEXT(MOD(COUNT($A1:A1)-IFERROR(MATCH(1,0/($A1:A1>=0)),0),4),"[=0]\4;0"))),A1)

though here I am abusing the unintended use of a negative number for that function's width parameter.

Regards

Another great work

 

[Vishal Srivastava]

I can get this:
View attachment 71359
In D3 the formula:

=CHOOSE(MAX(1,INDEX(FREQUENCY(IF(A2:D2<0,COLUMN(A2:D2)),IF(A2:D2>=0,COLUMN(A2:D2))),COUNTA(FREQUENCY(IF(A2:D2<0,COLUMN(A2:D2)),IF(A2:D2>=0,COLUMN(A2:D2)))))),D2,SUM(C2:D2),SUM(B2:D2),SUM(A2:D2))

which, depending on your version of Excel may need to be committed to the sheet with Ctrl+Shift+Enter rather than just Enter. Then copy to the right.
Cells B3 and C3 have variants of the same formula.
Cell A3 formula is ultra simple.
The last cell highlighted in yellow is my only concern; once you've found a sequence of 4 negatives you seem to start again - I gave up at that point!

Indeed Nice Try!

Thanks for your help!