Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 25
Default sumif 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


  #2   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default sumif with or< condition

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

On Apr 27, 1:39 am, "Will Fleenor" wrote:
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



  #3   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 3,355
Default sumif with or< condition

I got it to work with this:

=SUM(IF(ABS(A1:A6)20000,A1:A6))

Activate with CTRL SHIFT ENTER

"Will Fleenor" wrote:

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



  #4   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default sumif with or< condition



=Sumif(A1:A6,"20000")+Sumif(A1:A6,"<-20000")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Will Fleenor" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default sumif with or< condition

OR this:

=SUMPRODUCT(((A1:A62)+(A1:A6<-2))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...


=Sumif(A1:A6,"20000")+Sumif(A1:A6,"<-20000")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Will Fleenor" wrote in message
...
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







  #6   Report Post  
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel
external usenet poster
 
Posts: 3,101
Default sumif with or< condition

=SUMIF(A1:A6,"20000",A1:A6)+SUMIF(A1:A6,"<-20000",A1:A6)

"Will Fleenor" wrote:

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



  #7   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default sumif with or< condition

Of course, add the 0's:

=SUMPRODUCT(((A1:A620000)+(A1:A6<-20000))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
OR this:

=SUMPRODUCT(((A1:A62)+(A1:A6<-2))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...


=Sumif(A1:A6,"20000")+Sumif(A1:A6,"<-20000")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Will Fleenor" wrote in message
...
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







  #8   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default sumif with or< condition

One mo<g

=SUM(SUMIF(A1:A6,{"20000","<-20000"}))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Ragdyer" wrote in message
...
Of course, add the 0's:

=SUMPRODUCT(((A1:A620000)+(A1:A6<-20000))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
OR this:

=SUMPRODUCT(((A1:A62)+(A1:A6<-2))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...


=Sumif(A1:A6,"20000")+Sumif(A1:A6,"<-20000")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Will Fleenor" wrote in message
...
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








  #9   Report Post  
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default sumif with or< condition

Yet one more

=SUMPRODUCT(--(ABS(A1:A20)2000),A1:A20)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"RagDyeR" wrote in message
...
One mo<g

=SUM(SUMIF(A1:A6,{"20000","<-20000"}))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Ragdyer" wrote in message
...
Of course, add the 0's:

=SUMPRODUCT(((A1:A620000)+(A1:A6<-20000))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...
OR this:

=SUMPRODUCT(((A1:A62)+(A1:A6<-2))*A1:A6)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" wrote in message
...


=Sumif(A1:A6,"20000")+Sumif(A1:A6,"<-20000")


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit
!
---------------------------------------------------------------------------
"Will Fleenor" wrote in message
...
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










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sumif with or< condition Will Fleenor Excel Discussion (Misc queries) 8 April 27th 07 07:45 AM
SUMIF - Compound Condition Mike McLellan Excel Discussion (Misc queries) 3 January 11th 06 11:56 AM
sumif on more than one condition steve alcock Links and Linking in Excel 4 May 13th 05 01:53 PM
Multiple Condition Sumif Formula momtoaj Excel Worksheet Functions 3 April 6th 05 04:06 PM
Is it possible to specify multiple condition with SUMIF? Daniel Excel Worksheet Functions 1 November 17th 04 02:38 AM


All times are GMT +1. The time now is 10:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"