SUMif exclusing negative values

[Stanman]

Hi,

I want to sum a range that has both positive and negative numbers but would like to exclude either. I have tried =SUMIF(Sheet!X2:X2000,"AD",Sheet!D2:D2000, "<0") but it gave a calculation error.

Any help would be appreciated

Thanks

 

[GraH]

You have one argument to many for Countif. Try the same with COUNTIFS.

 

[Stanman]

If I use count, wont that return just the numbers, I was after the total in the column based on the criteria but excluding negative numbers

 

[RoryA]

You need SUMIFS, which takes the sum range first, so to sum negatives:

=SUMIFS(Sheet!D2:D2000,Sheet!X2:X2000,"AD",Sheet!D2:D2000, "<0")

 

[Peter_SSs]

I was after the total in the column based on the criteria but excluding negative numbers

Try

=SUMIFS(Sheet!D2:D2000,Sheet!X2:X2000,"AD",Sheet!D2:D2000, ">0")

Edit: I missed Rory's post but I think the formula needs SUMIFS, which his post states but missed the 'S' in the formula.

 

[Sufiyan97]

Try

Book2
	A	B	C	D
1	Names	Amount	Excluding Negative	Excluding Positive
2	AD	1	6	-2
3	AF	1
4	AF	1
5	AF	1
6	AF	1
7	AF	1
8	AD	1
9	AD	1
10	AD	1
11	AD	1
12	AD	1
13	AD	-1
14	AD	-1
Sheet1

Cell Formulas
Range	Formula
C2	C2	=SUMIFS(B:B,A:A,"AD",B:B,">"&0)
D2	D2	=SUMIFS(B:B,A:A,"AD",B:B,"<"&0)

 

[Sufiyan97]

You need SUMIFS, which takes the sum range first, so to sum negatives:

=SUMIF(Sheet!D2:D2000,Sheet!X2:X2000,"AD",Sheet!D2:D2000, "<0")

Isn't it

=SUMIFS(Sheet!D2:D2000,Sheet!X2:X2000,"AD",Sheet!D2:D2000, "<0")

 

[RoryA]

Yes, as Peter already pointed out - I copied and pasted the original and forgot to add the S (though I have edited my post now).

 

[GraH]

Haha, don't know at all why I switched to COUNTIF and proposed COUNTIFS. Temporary insanity or so.