sum negative and positive values separately

[bora]

Hi,
I have a time series like this:

1995, -.05
1996, -2.1
1997, 3.6
1998, 4.3
1999, -0.7
2000, -0.08

and I would like to sum just the positive and negative value separately
in this way

1995, -2.15
1997, 7.9
1999, -0.78

And I don't know if it is

 

[Bernard Liengme]

To sum only positive values use =SUMIF(B1:B10,">0") and to sum only negative
values =SUMIF(B1:B10,"<0")
But I cannot see how you get the second tables from the first.
best wishes

 

[bora]

I know how to use SUMIF,
The second table show the sum of consecutive positive or negative
values at thihe first time of occurrence.
regards

Bernard Liengme ha scritto:

 

[Dave Peterson]

And to get the sum of all the 0's:

=sumif(b1:b10,0)
or
just
0

<well, it made me smile>

 

[Bernard Liengme]

Surely a problem that complex needs SUMPRODUCT <bg>

 

[PapaDos]

--
Regards,
Luc.

"Festina Lente"

 

[PapaDos]

Try this ARRAY FORMULA in D1

=IF( ROWS($1:1) < SUM( --( SIGN( $B$2:$B$6 ) <> SIGN( $B$1:$B$5 ) ) ) + 2,
INDEX($A:$A, IF( ROWS($1:1) = 1, ROW( $B$1:$B$5 ), SMALL( ( SIGN( $B$2:$B$6 )
<> SIGN( $B$1:$B$5 ) ) * ROW( $B$2:$B$6 ), ROWS($1:1) + SUM( --( SIGN(
$B$2:$B$6 ) = SIGN( $B$1:$B$5 ) ) ) - 1 ) ) ), "" )

And this ARRAY FORMULA in E1

=IF( ISNUMBER( D1 ), SUMPRODUCT( ( $A$1:$A$6 >= D1 ) * ( $A$1:$A$6 < IF(
ISNUMBER( D2 ), D2, MAX( $A$1:$A$6 ) + 1 ) ) * ( $B$1:$B$6 ) ), "" )

Drag/Fill down as needed...

 

[PapaDos]

These forums are driving me nuts, I have been having trouble posting
correctly tonight...

My last reply's second formula (E1) is NOT an array formula.

An alternative to it is this ARRAY FORMULA in E1

=IF( ISNUMBER( D1 ), SUM( ( $A$1:$A$6 >= D1 ) * ( IF( ISNUMBER( D2 ),
$A$1:$A$6 < D2, 1 ) ) * ( $B$1:$B$6 ) ), "" )