View Full Version : [SOLVED:] 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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.