Using =sumif to add up just the positive numbers and ignore minus???

[smerrick]

Hello,

I need to use formulas such as =sumif but was wondering if this could be amended so that when I add a column it will only take into account positive numbers and not the negatives? Failing that, would there be an alternative way to quickly do this?

Many Thanks

 

[GlennUK]

Did you try SUMIF with a criteria of ">0"?

 

[smerrick]

Not yet - how would that work using a simple example?

 

[GlennUK]

Like:
=SUMIF(myrange,">0",myrange)

 

[marka87uk]

=sumif(a:a,">0")

 

[bebo021999]

Try this:

=SUMPRODUCT((A1:A4>0)*(A1:A4))

With your range from A1 to A4

 

[smerrick]

Thanks for that. Just to expand on this, say that I needed to lookup product codes. Column A would have the product code, column B the quantity of which some would be negative. What I am trying to do is to use the sumif formula on a product code but just to add up all of the positive quantites. Is this possibe?

 

[bebo021999]

What I am trying to do is to use the sumif formula on a product code but just to add up all of the positive quantites. Is this possibe?

Adjust to:

=SUMPRODUCT((A1:A4="product code")*(B1:B4>0)*(B1:B4))

For each line, Excel will check if 3 criteria is fulfilled or not, if yes, sumproduct with sum up the quantities.

 

[smerrick]

trying to avoid sumproduct if I can as it makes up a whole lot of memory - working with a hugh file. Any alternatives, especially using sumif?

 

[GlennUK]

Do you have Excel 2007? That has SUMIFS for multiple criteria.