Sum a column that has positive and negative values

[julhs]

I’m flummoxed as to the correct formula that I need to return the expected/desired result.
“Total Owed” in D18 is the desired outcome but “Total Owed” in H14 is not giving the expected/desired result.
All data is entered as numbers and not text formatted as a number.

Accounts 2016 - 2019 Final Currant Master New New (Autosaved).xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4
5				10.00				-10.00
6				90.21				90.21
7				89.95				89.95
8				89.95				89.95
9				250.00				-250.00
10				89.95				89.95
11				126.19				126.19
12				89.95				89.95
13				50.00				50.00
14				886.20		Total owed		366.20
15
16				-10.00
17				-250.00
18		Total owed		626.20
19
20
Sheet3

Cell Formulas
Range	Formula
D14,H14	D14	=SUM(D5:D13)
D18	D18	=SUM(D14:D17)

 

[Joe4]

I don't see the issue.
Which value is wrong and what exactly do you expect it to be?

 

[KRice]

Your two examples are not the same sum. On the left, you are reversing the sum of 10 and 250...so those effectively are zeroed out. On the right, you are actually subtracting 10 and 250...so 366.20 is correct. On the left, you would need to subtract another 10 and 250.

 

[Joe4]

I was looking for some mathematical/computational error and not finding one (because there isn't one).
Sounds like Kirk probably identified their misunderstanding of the situation/data, and shows why the two columns are not equivalent!

 

[Scott Huish]

If you are trying to sum the values as positive whether they are entered as positive or negative, then do this:

=SUM(ABS(H5:H13))

 

[julhs]

This is a bit of a catch all reply.
I’m not sure how better to describe things, so excuse me for the simplicity.
Think of this like using a calculator.
1+1=2
you then subtract 3 and the result is “-1”
you then add 5 and the result is “4”
So it comes down to how the formula in H14 handles the minus values in the column and returns the same value that’s in D18.

Specifically to Scott.
Tried that previously but it returns a #Value error.

 

[KRice]

Did you follow my post #3? In the column D calculation, you've added 10 and 250 to obtain a sum of 886.20. In column H, you've actually subtracted 10 and 250 (that is, you've added -10 and -250) to obtain a sum of 366.20. The point is that in the lower calculation in column D, if you want to obtain the same result of 366.20, you would not simply subtract 10 and 250....that only zeroes out the prior addition of those two numbers (10 and 250). You need to subtract them one more time...so 886.20-10-10-250-250=366.2. There is nothing unusual about how SUM is handling the values.

 

[dreid1011]

This is a bit of a catch all reply.
I’m not sure how better to describe things, so excuse me for the simplicity.
Think of this like using a calculator.
1+1=2
you then subtract 3 and the result is “-1”
you then add 5 and the result is “4”
So it comes down to how the formula in H14 handles the minus values in the column and returns the same value that’s in D18.

Specifically to Scott.
Tried that previously but it returns a #Value error.

Per your example, the only way to get the same result is to ignore the negative values.. otherwise, the formula is working as it is intended to work.

 

[julhs]

I appreciate all the advice so far, but I’m going to have to take a time out on it and come back to it tomorrow.

 

[Joe4]

In a nutshell, excluding the sums, the data in your two lists (columns) is NOT the same, as Kirk explained.
So it should not be returning the same result.

The formulas are all solid. Your logic/understanding is just a little faulty.
Maybe when you come back to it tomorrow with fresh eyes it will be more clear to you.