Summing Negatives

B

Brad

I would like to create a formula that will sum only the negatives in a
column. Is this possible?
 
B

Brad

Can I expand that formula to take criteria from another column into creteria?
What I mean is let's say I also want to add the number from a1 to a10 if the
quantity in b1 to b10 is less than 0?
 
D

Dave R.

You can add
,B1:B10 after the "<0"



Brad said:
Can I expand that formula to take criteria from another column into creteria?
What I mean is let's say I also want to add the number from a1 to a10 if the
quantity in b1 to b10 is less than 0?
Click to expand...
 
D

Dave R.

My mistake- actually, you'd change A1:A10 to B1:B10, then add in ,A1:A10
after "<0"
 
B

Brad

My mistake, what I'm looking for the sum of quantities in a1 to a10 if they
are less than 0 and the quantity in b1 to b10 is GREATER than 0.

Can it be done?
 
A

Alex Delamain

almost anything is possible!

try this

=SUMPRODUCT((A1:A10<0)*(B1:B10>0)*(A1:A10)
 
P

Peo Sjoblom

If you want the sum of <0 in A where B is >0
example

-1 0
-2 4
-1 10

would return 11

=SUMPRODUCT(--(A1:A10<0),--(B1:B10>0),A1:A10+B1:B10)

if you just want to sum all <0 in A and all >0 in B together use

=SUMIF(A1:A10,"<0")+SUMIF(B1:B10,">0")

Regards,

Peo Sjoblom
 
Top