PDA

View Full Version : Sumif & sum only positive or negative number



excelliot
03-06-2007, 03:04 AM
I m trying using sumif() only positive nuber which satisfied given citerion, but im getting result "0". CAn anybody help me. Formula i have entered is "=sumif(A1:A100,A110&>0,B1:100)".

Here i m first trying to sum all cells in ranges which is equal to cell A110 & greater than zero, but i got only donuts??:doh: :think:

Bob Phillips
03-06-2007, 03:25 AM
=SUMPRODUCT(--(A1:A100=A110),--(B1:B100>0),B1:B100)

excelliot
03-06-2007, 03:39 AM
=SUMPRODUCT(--(A1:A100=A110),--(B1:B100>0),B1:B100)

I have found this formula =SUMPRODUCT[A1:A100, (A1:A100>0)*(B1:B100=A169)] it is workin

Can you explain me yr formula step by step. it is very short. IS it possible to combining sumif() fn with other??:think:

Aussiebear
03-06-2007, 03:56 AM
Hi Excelliot,

In your initial post you appear to be trying to sumif two different arrays. Bob has suggested that you use the SUMPRODUCT procedure to do so.


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

Your first array is A1:A100 = A110 and the second being B1:B100>0. By using Sumproduct you can use up to 255 arrays in the formula.

Please refer to the help file of Excel for an example of how this is completed.

Ted

excelliot
03-06-2007, 04:01 AM
what is use of "--" (that two dashes) in that 4mula

excelliot
03-06-2007, 04:07 AM
I think -- sign indicates that this is dependent condition??

Bob Phillips
03-06-2007, 04:52 AM
I think -- sign indicates that this is dependent condition??

See http://xldynamic.com/source/xld.SUMPRODUCT.html