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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.