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)

 

[julhs]

This is post has not gone terribly well due to my poor explanation and example data.
How right you are Joe!

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.

So with pointers from everyone and research, I figured out how to resolve my issue.
The problem I was trying to resolve was caused by the mistake I made of applying "-" identifiers to some values in "H5:H13" and thinking that a straight forward "Sum" of the column would give me the Total of 626.20 (So a case of me NOT understanding how SUM evaluated the "-" values)
I researched further and it was as simple as using the below that now gives me the intended value of 626.20

=SUMIF(H5:H13,">0")

Thanks everyone for their input.
Julhs

 

[KRice]

Thanks for the explanation. I'm curious...are the values actually negative, or did you just happen to make the unfortunate choice to use a hyphen (subtraction operator or negative sign) as the character for flagging certain positive values that should be ignored? Some other options: you could use nearly any other text character in front of the value, and that causes Excel to treat the cell contents as text, so it would be ignored by the SUM function (see column F, where SUM is used...or SUMIF if you prefer). Alternatively, and probably preferred because it is easy to inspect and doesn't change the original cell values, you could use a helper column to identify values that should be ignored, and flag those values with some character (see columns H:I where the letter "z" is used and then SUMIF is used to ignore any values on rows where "z" appears in column H).

MrExcel_20240131_C (version 1).xlsx
	C	D	E	F	G	H	I
4						Ignore	Values
5		-10.00		x10		z	10.00
6		90.21		90.21			90.21
7		89.95		89.95			89.95
8		89.95		89.95			89.95
9		-250.00		x250		z	250.00
10		89.95		89.95			89.95
11		126.19		126.19			126.19
12		89.95		89.95			89.95
13		50.00		50.00			50.00
14	Sum	366.20		626.20			626.20
15	Desired Sum ignores negative values	626.20
16
17	Sum only positive values	626.20		626.20
Sheet6

Cell Formulas
Range	Formula
D14,F14	D14	=SUM(D5:D13)
I14	I14	=SUMIF(H5:H13,"<>z",I5:I13)
D17,F17	D17	=SUMIF(D5:D13,">0")

 

[julhs]

Kirk
The values are ONLY negative because I (naively) thought that by MANUALLY making them so was the way to subtract their value in my ORIGINAL "Sum" formula.
As I said in #11,

(So a case of me NOT understanding how SUM evaluated the "-" values)

I have NO doubt that what you have provided would work; but as I have had to resort to other "Helper" columns (for other reasons, primarily due to my lack of knowledge) that I’m reluctant to add any more when;

=SUMIF(H5:H13,">0")

works for me without a further helper column to achieve what I'm trying to do in this senario.