Summing Negatives

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to create a formula that will sum only the negatives in a
column. Is this possible?
 
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?
 
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...
 
My mistake- actually, you'd change A1:A10 to B1:B10, then add in ,A1:A10
after "<0"
 
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?
 
almost anything is possible!

try this

=SUMPRODUCT((A1:A10<0)*(B1:B10>0)*(A1:A10)
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excell - two conditions count if factors are true when negative nu 2
sum function 1
SUm 2
Need Help with "SUM" AND "ABS" in same formula 12
adding positive and negative numbers 3
If sign of sum is negative change to positive 1
Subtotaling minus negative numbers and posative numbers 3
autosum refusal to sum highlighted range 4

Back
Top