 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / April 2007sumif with >or< condition
I have 6 numbers in cells a1..a6. Some are positive and some are negative. I would like to total up only the numbers that are > 20000 or <-20000. This formula works for > =SUMIF(A1:A6,">"&20000,A1:A6) but this formula will not work: =SUMIF(A1:A6,or(">"&20000,A1:A6,"<"&-20000),A1:A6 ) What is wrong with the last formula. It always evaluates to 0. Thanks Will You can only have one condition with SUMIF. Try this: =SUMIF(A1:A6,">"&20000,A1:A6)+SUMIF(A1:A6,"<"&-20000,A1:A6) Hope this helps. Pete > I have 6 numbers in cells a1..a6. Some are positive and some are negative. > I would like to total up only the numbers that are > 20000 or <-20000. [quoted text clipped - 5 lines] > > Thanks Will I got it to work with this: =SUM(IF(ABS(A1:A6)>20000,A1:A6)) Activate with CTRL SHIFT ENTER > I have 6 numbers in cells a1..a6. Some are positive and some are negative. > I would like to total up only the numbers that are > 20000 or <-20000. [quoted text clipped - 5 lines] > > Thanks Will =Sumif(A1:A6,">20000")+Sumif(A1:A6,"<-20000")  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- >I have 6 numbers in cells a1..a6. Some are positive and some are negative. >I would like to total up only the numbers that are > 20000 or <-20000. [quoted text clipped - 5 lines] > > Thanks Will OR this: =SUMPRODUCT(((A1:A6>2)+(A1:A6<-2))*A1:A6) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > > =Sumif(A1:A6,">20000")+Sumif(A1:A6,"<-20000") [quoted text clipped - 9 lines] >> >> Thanks Will Of course, add the 0's: =SUMPRODUCT(((A1:A6>20000)+(A1:A6<-20000))*A1:A6) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > OR this: > [quoted text clipped - 12 lines] >>> >>> Thanks Will One more:<g> =SUM(SUMIF(A1:A6,{">20000","<-20000"})) SignatureHTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== Of course, add the 0's: =SUMPRODUCT(((A1:A6>20000)+(A1:A6<-20000))*A1:A6) SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ragdyer" <ragdyer@cutoutmsn.com> wrote in message news:%23yboviGiHHA.3472@TK2MSFTNGP04.phx.gbl... > OR this: > [quoted text clipped - 12 lines] >>> >>> Thanks Will Yet one more =SUMPRODUCT(--(ABS(A1:A20)>2000),A1:A20) SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > One more:<g> > [quoted text clipped - 20 lines] >>>> >>>> Thanks Will =SUMIF(A1:A6,">20000",A1:A6)+SUMIF(A1:A6,"<-20000",A1:A6) > I have 6 numbers in cells a1..a6. Some are positive and some are negative. > I would like to total up only the numbers that are > 20000 or <-20000. [quoted text clipped - 5 lines] > > Thanks Will |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.