Interesting Question

[KOK]

This is an interesting question that requires lot of thinking....

Genrally sumif formula is like this

SUMIF(range,criteria,sumrange)

My question is that how I make the formula to count the sum of only positive numbers or negetive numbers ?
For Eg : Range is A:A, Criteria is "ABC" and the Sumrange is B:B having positive and negative values, now I want formula to count the sum of only positvie numbers or
negative numbers ..HOW CAN I DO THIS ?

Thanks in Advance..!!

 

[VoG]

Maybe

=SUMPRODUCT(--(A1:A100="ABC"),--(B1:B100>0),B1:B100)

 

[RoryA]

If you are using Excel 2007 or later you can use SUMIFS but otherwise you need SUMPRODUCT:
=SUMPRODUCT(--(range1=criteria1),--(sumrange>0),sumrange)
for example.
Note that you cannot use entire columns as the range references.

 

[jasonb75]

Try

=sumproduct(--(range=criteria),--(sumrange>0),sumrange)

Change > to < for negative results.

 

[KOK]

You are great.. Thanks…!!