Sum of Negative and Positive Values in single column

[Krayziekitten]

Hello,

I am trying to have excel total all of the negative amount and positive amounts out of a single column by identifying if each cell is above or below zero.
I have been trying a few different formulas, but not working out for me.

Here is example data:

COLUMN
-40.00
10.00
12.00
-100.00
-1.00
22.00

Negative should amount to: -141.00
Positive should amount to: 44.00

Thank you!

 

[63falcondude]

Negative: =SUMIF(A1:A6,"<0",A1:A6)
Positive: =SUMIF(A1:A6,">0",A1:A6)

 

[Jonmo1]

Try

=SUMIF(A:A,">0")
and
=SUMIF(A:A,"<0")

 

[Krayziekitten]

I have tried both of those and Excel keeps giving me the error I need an apostrophe.

 

[Jonmo1]

Try changing the commas to semicolons ( , to ; )

 

[Krayziekitten]

It doesn't like the semi-colons either. Could it be a formatting issue? It isn't liking even an IF formula.

 

[Krayziekitten]

ah I got it -- It wants the pipe delimiter instead of comma or semi-colon

 

[Jonmo1]

It must be a problem with the list separator character.
It's usually a comma, or sometimes a semicolon ;
But it actually could be anything.

Easy way to find out what that character is supposed to be...

Type =SUM(
Hold down the CTRL Key
Randomly select a few cells (not next to each other)
Press Enter

Look at the resulting formula, what character shows between each cell reference.

 

[Krayziekitten]

Thanks! I forgot I changed my delimiter to a pipe from comma this morning.

 

[Jonmo1]

You're welcome.