absolute function - range  
Author Message
TazMan





PostPosted: Wed Oct 26 12:17:04 CDT 2005 Top

Excel Misc >> absolute function - range

I am trying to count cells in selected range that are above 50 and below -50.
How can I do this without creating another row with absolute values. I
tried this but it doesn't work

=countif(abs(u2:u2000),">50").

Excel162  
 
 
Elkar





PostPosted: Wed Oct 26 12:17:04 CDT 2005 Top

Excel Misc >> absolute function - range Try this:

=countif(u2:u2000,">50")+countif(u2:u2000,"<-50")




> I am trying to count cells in selected range that are above 50 and below -50.
> How can I do this without creating another row with absolute values. I
> tried this but it doesn't work
>
> =countif(abs(u2:u2000),">50").
 
 
Zack





PostPosted: Wed Oct 26 12:22:34 CDT 2005 Top

Excel Misc >> absolute function - range Hi there Aleks,

You can use the following ...

=SUMPRODUCT(--(ABS(U2:U2000)>50))

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.






>I am trying to count cells in selected range that are above 50 and
>below -50.
> How can I do this without creating another row with absolute values. I
> tried this but it doesn't work
>
> =countif(abs(u2:u2000),">50").


 
 
Aleks





PostPosted: Wed Oct 26 15:30:06 CDT 2005 Top

Excel Misc >> absolute function - range Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to (-50)?





> Hi there Aleks,
>
> You can use the following ...
>
> =SUMPRODUCT(--(ABS(U2:U2000)>50))
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> To email, remove the NO SPAM. Please keep correspondence to the board, as
> to benefit others.
>
>
>
>


> >I am trying to count cells in selected range that are above 50 and
> >below -50.
> > How can I do this without creating another row with absolute values. I
> > tried this but it doesn't work
> >
> > =countif(abs(u2:u2000),">50").
>
>
>
 
 
Zack





PostPosted: Wed Oct 26 15:54:57 CDT 2005 Top

Excel Misc >> absolute function - range Possibly ..

=SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))

Note that this will not count those equal to 50.

HTH

--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.





> Outstanding!!! Thank yoy!
> How about
> counting cells in selected range that are btw 5 to 50 and (-5) to (-50)?
>
>
>

>
>> Hi there Aleks,
>>
>> You can use the following ...
>>
>> =SUMPRODUCT(--(ABS(U2:U2000)>50))
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> To email, remove the NO SPAM. Please keep correspondence to the board,
>> as
>> to benefit others.
>>
>>
>>
>>


>> >I am trying to count cells in selected range that are above 50 and
>> >below -50.
>> > How can I do this without creating another row with absolute values. I
>> > tried this but it doesn't work
>> >
>> > =countif(abs(u2:u2000),">50").
>>
>>
>>


 
 
Aleks





PostPosted: Wed Oct 26 16:45:05 CDT 2005 Top

Excel Misc >> absolute function - range This is awesome! Zack, whoever you are, you're a GOD!



> Possibly ..
>
> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>
> Note that this will not count those equal to 50.
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> To email, remove the NO SPAM. Please keep correspondence to the board, as
> to benefit others.
>
>
>


> > Outstanding!!! Thank yoy!
> > How about
> > counting cells in selected range that are btw 5 to 50 and (-5) to (-50)?
> >
> >
> >

> >
> >> Hi there Aleks,
> >>
> >> You can use the following ...
> >>
> >> =SUMPRODUCT(--(ABS(U2:U2000)>50))
> >>
> >> HTH
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >> To email, remove the NO SPAM. Please keep correspondence to the board,
> >> as
> >> to benefit others.
> >>
> >>
> >>
> >>


> >> >I am trying to count cells in selected range that are above 50 and
> >> >below -50.
> >> > How can I do this without creating another row with absolute values. I
> >> > tried this but it doesn't work
> >> >
> >> > =countif(abs(u2:u2000),">50").
> >>
> >>
> >>
>
>
>
 
 
Anne





PostPosted: Wed Oct 26 17:35:30 CDT 2005 Top

Excel Misc >> absolute function - range I've been trying to tell him that for a year. Here's Zack!
http://www.vbaexpress.com/forum/member.php?u=11
************
Anne Troy
www.OfficeArticles.com



> This is awesome! Zack, whoever you are, you're a GOD!
>

>
>> Possibly ..
>>
>> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>>
>> Note that this will not count those equal to 50.
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> To email, remove the NO SPAM. Please keep correspondence to the board,
>> as
>> to benefit others.
>>
>>
>>


>> > Outstanding!!! Thank yoy!
>> > How about
>> > counting cells in selected range that are btw 5 to 50 and (-5) to
>> > (-50)?
>> >
>> >
>> >

>> >
>> >> Hi there Aleks,
>> >>
>> >> You can use the following ...
>> >>
>> >> =SUMPRODUCT(--(ABS(U2:U2000)>50))
>> >>
>> >> HTH
>> >>
>> >> --
>> >> Regards,
>> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>> >> To email, remove the NO SPAM. Please keep correspondence to the
>> >> board,
>> >> as
>> >> to benefit others.
>> >>
>> >>
>> >>
>> >>


>> >> >I am trying to count cells in selected range that are above 50 and
>> >> >below -50.
>> >> > How can I do this without creating another row with absolute values.
>> >> > I
>> >> > tried this but it doesn't work
>> >> >
>> >> > =countif(abs(u2:u2000),">50").
>> >>
>> >>
>> >>
>>
>>
>>


 
 
Aleks





PostPosted: Wed Oct 26 19:25:03 CDT 2005 Top

Excel Misc >> absolute function - range :)

so here is another one ...
I still have the same list u2:u2000 and there are both positive and negative
numbers. I need to get a sum of all number >50 and <-50.

again, I bow to Zack.



> I've been trying to tell him that for a year. Here's Zack!
> http://www.vbaexpress.com/forum/member.php?u=11
> ************
> Anne Troy
> www.OfficeArticles.com
>


> > This is awesome! Zack, whoever you are, you're a GOD!
> >

> >
> >> Possibly ..
> >>
> >> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
> >>
> >> Note that this will not count those equal to 50.
> >>
> >> HTH
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >> To email, remove the NO SPAM. Please keep correspondence to the board,
> >> as
> >> to benefit others.
> >>
> >>
> >>


> >> > Outstanding!!! Thank yoy!
> >> > How about
> >> > counting cells in selected range that are btw 5 to 50 and (-5) to
> >> > (-50)?
> >> >
> >> >
> >> >

> >> >
> >> >> Hi there Aleks,
> >> >>
> >> >> You can use the following ...
> >> >>
> >> >> =SUMPRODUCT(--(ABS(U2:U2000)>50))
> >> >>
> >> >> HTH
> >> >>
> >> >> --
> >> >> Regards,
> >> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >> >> To email, remove the NO SPAM. Please keep correspondence to the
> >> >> board,
> >> >> as
> >> >> to benefit others.
> >> >>
> >> >>
> >> >>
> >> >>


> >> >> >I am trying to count cells in selected range that are above 50 and
> >> >> >below -50.
> >> >> > How can I do this without creating another row with absolute values.
> >> >> > I
> >> >> > tried this but it doesn't work
> >> >> >
> >> >> > =countif(abs(u2:u2000),">50").
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
 
 
Roger





PostPosted: Wed Oct 26 19:38:08 CDT 2005 Top

Excel Misc >> absolute function - range Hi Aleks

Just add another range to Zack's original formula
=SUMPRODUCT(--(ABS(U2:U2000)>50),U2:U2000)

Regards

Roger Govier



> :)
>
> so here is another one ...
> I still have the same list u2:u2000 and there are both positive and negative
> numbers. I need to get a sum of all number >50 and <-50.
>
> again, I bow to Zack.
>

>
>
>>I've been trying to tell him that for a year. Here's Zack!
>>http://www.vbaexpress.com/forum/member.php?u=11
>>************
>>Anne Troy
>>www.OfficeArticles.com
>>


>>
>>>This is awesome! Zack, whoever you are, you're a GOD!
>>>

>>>
>>>
>>>>Possibly ..
>>>>
>>>>=SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>>>>
>>>>Note that this will not count those equal to 50.
>>>>
>>>>HTH
>>>>
>>>>--
>>>>Regards,
>>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>To email, remove the NO SPAM. Please keep correspondence to the board,
>>>>as
>>>>to benefit others.
>>>>
>>>>
>>>>


>>>>
>>>>>Outstanding!!! Thank yoy!
>>>>>How about
>>>>>counting cells in selected range that are btw 5 to 50 and (-5) to
>>>>>(-50)?
>>>>>
>>>>>
>>>>>

>>>>>
>>>>>
>>>>>>Hi there Aleks,
>>>>>>
>>>>>>You can use the following ...
>>>>>>
>>>>>>=SUMPRODUCT(--(ABS(U2:U2000)>50))
>>>>>>
>>>>>>HTH
>>>>>>
>>>>>>--
>>>>>>Regards,
>>>>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>>>To email, remove the NO SPAM. Please keep correspondence to the
>>>>>>board,
>>>>>>as
>>>>>>to benefit others.
>>>>>>
>>>>>>
>>>>>>
>>>>>>


>>>>>>
>>>>>>>I am trying to count cells in selected range that are above 50 and
>>>>>>>below -50.
>>>>>>>How can I do this without creating another row with absolute values.
>>>>>>>I
>>>>>>>tried this but it doesn't work
>>>>>>>
>>>>>>>=countif(abs(u2:u2000),">50").
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>
>>
 
 
Aleks





PostPosted: Wed Oct 26 20:15:04 CDT 2005 Top

Excel Misc >> absolute function - range This is beautiful!!!

How about the same as below but sum up only values that have "yes" a2:a2000



> Hi Aleks
>
> Just add another range to Zack's original formula
> =SUMPRODUCT(--(ABS(U2:U2000)>50),U2:U2000)
>
> Regards
>
> Roger Govier
>
>

> > :)
> >
> > so here is another one ...
> > I still have the same list u2:u2000 and there are both positive and negative
> > numbers. I need to get a sum of all number >50 and <-50.
> >
> > again, I bow to Zack.
> >

> >
> >
> >>I've been trying to tell him that for a year. Here's Zack!
> >>http://www.vbaexpress.com/forum/member.php?u=11
> >>************
> >>Anne Troy
> >>www.OfficeArticles.com
> >>


> >>
> >>>This is awesome! Zack, whoever you are, you're a GOD!
> >>>

> >>>
> >>>
> >>>>Possibly ..
> >>>>
> >>>>=SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
> >>>>
> >>>>Note that this will not count those equal to 50.
> >>>>
> >>>>HTH
> >>>>
> >>>>--
> >>>>Regards,
> >>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>>>To email, remove the NO SPAM. Please keep correspondence to the board,
> >>>>as
> >>>>to benefit others.
> >>>>
> >>>>
> >>>>


> >>>>
> >>>>>Outstanding!!! Thank yoy!
> >>>>>How about
> >>>>>counting cells in selected range that are btw 5 to 50 and (-5) to
> >>>>>(-50)?
> >>>>>
> >>>>>
> >>>>>

> >>>>>
> >>>>>
> >>>>>>Hi there Aleks,
> >>>>>>
> >>>>>>You can use the following ...
> >>>>>>
> >>>>>>=SUMPRODUCT(--(ABS(U2:U2000)>50))
> >>>>>>
> >>>>>>HTH
> >>>>>>
> >>>>>>--
> >>>>>>Regards,
> >>>>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>>>>>To email, remove the NO SPAM. Please keep correspondence to the
> >>>>>>board,
> >>>>>>as
> >>>>>>to benefit others.
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>


> >>>>>>
> >>>>>>>I am trying to count cells in selected range that are above 50 and
> >>>>>>>below -50.
> >>>>>>>How can I do this without creating another row with absolute values.
> >>>>>>>I
> >>>>>>>tried this but it doesn't work
> >>>>>>>
> >>>>>>>=countif(abs(u2:u2000),">50").
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>
> >>>>
> >>
> >>
>
 
 
Roger





PostPosted: Wed Oct 26 20:25:05 CDT 2005 Top

Excel Misc >> absolute function - range Hi Aleks

Try
=SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000)

You need to read up on how Sumproduct works, then you can do any of these
tasks very easily. A good starting point is
http://xldynamic.com/source/xld.SUMPRODUCT.html

Regards

Roger Govier



> This is beautiful!!!
>
> How about the same as below but sum up only values that have "yes" a2:a2000
>

>
>
>>Hi Aleks
>>
>>Just add another range to Zack's original formula
>>=SUMPRODUCT(--(ABS(U2:U2000)>50),U2:U2000)
>>
>>Regards
>>
>>Roger Govier
>>
>>

>>
>>>:)
>>>
>>>so here is another one ...
>>>I still have the same list u2:u2000 and there are both positive and negative
>>>numbers. I need to get a sum of all number >50 and <-50.
>>>
>>>again, I bow to Zack.
>>>

>>>
>>>
>>>
>>>>I've been trying to tell him that for a year. Here's Zack!
>>>>http://www.vbaexpress.com/forum/member.php?u=11
>>>>************
>>>>Anne Troy
>>>>www.OfficeArticles.com
>>>>


>>>>
>>>>
>>>>>This is awesome! Zack, whoever you are, you're a GOD!
>>>>>

>>>>>
>>>>>
>>>>>
>>>>>>Possibly ..
>>>>>>
>>>>>>=SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>>>>>>
>>>>>>Note that this will not count those equal to 50.
>>>>>>
>>>>>>HTH
>>>>>>
>>>>>>--
>>>>>>Regards,
>>>>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>>>To email, remove the NO SPAM. Please keep correspondence to the board,
>>>>>>as
>>>>>>to benefit others.
>>>>>>
>>>>>>
>>>>>>


>>>>>>
>>>>>>
>>>>>>>Outstanding!!! Thank yoy!
>>>>>>>How about
>>>>>>>counting cells in selected range that are btw 5 to 50 and (-5) to
>>>>>>>(-50)?
>>>>>>>
>>>>>>>
>>>>>>>

>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>>Hi there Aleks,
>>>>>>>>
>>>>>>>>You can use the following ...
>>>>>>>>
>>>>>>>>=SUMPRODUCT(--(ABS(U2:U2000)>50))
>>>>>>>>
>>>>>>>>HTH
>>>>>>>>
>>>>>>>>--
>>>>>>>>Regards,
>>>>>>>>Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>>>>>To email, remove the NO SPAM. Please keep correspondence to the
>>>>>>>>board,
>>>>>>>>as
>>>>>>>>to benefit others.
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>


>>>>>>>>
>>>>>>>>
>>>>>>>>>I am trying to count cells in selected range that are above 50 and
>>>>>>>>>below -50.
>>>>>>>>>How can I do this without creating another row with absolute values.
>>>>>>>>>I
>>>>>>>>>tried this but it doesn't work
>>>>>>>>>
>>>>>>>>>=countif(abs(u2:u2000),">50").
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>
 
 
Roger





PostPosted: Wed Oct 26 20:28:02 CDT 2005 Top

Excel Misc >> absolute function - range Sorry, typo
that should be
=SUMPRODUCT(--(A2:A2000="yes"),U2:U2000)

Regards

Roger Govier



> Hi Aleks
>
> Try
> =SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000)
>
> You need to read up on how Sumproduct works, then you can do any of
> these tasks very easily. A good starting point is
> http://xldynamic.com/source/xld.SUMPRODUCT.html
>
> Regards
>
> Roger Govier
>
>

>
>> This is beautiful!!!
>>
>> How about the same as below but sum up only values that have "yes"
>> a2:a2000
>>

>>
>>
>>> Hi Aleks
>>>
>>> Just add another range to Zack's original formula
>>> =SUMPRODUCT(--(ABS(U2:U2000)>50),U2:U2000)
>>>
>>> Regards
>>>
>>> Roger Govier
>>>
>>>

>>>
>>>> :)
>>>>
>>>> so here is another one ...
>>>> I still have the same list u2:u2000 and there are both positive and
>>>> negative numbers. I need to get a sum of all number >50 and <-50.
>>>> again, I bow to Zack.
>>>>

>>>>
>>>>
>>>>
>>>>> I've been trying to tell him that for a year. Here's Zack!
>>>>> http://www.vbaexpress.com/forum/member.php?u=11
>>>>> ************
>>>>> Anne Troy
>>>>> www.OfficeArticles.com
>>>>>


>>>>>
>>>>>
>>>>>> This is awesome! Zack, whoever you are, you're a GOD!
>>>>>>

>>>>>>
>>>>>>
>>>>>>
>>>>>>> Possibly ..
>>>>>>>
>>>>>>> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>>>>>>>
>>>>>>> Note that this will not count those equal to 50.
>>>>>>>
>>>>>>> HTH
>>>>>>>
>>>>>>> --
>>>>>>> Regards,
>>>>>>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>>>> To email, remove the NO SPAM. Please keep correspondence to the
>>>>>>> board, as
>>>>>>> to benefit others.
>>>>>>>
>>>>>>>
>>>>>>>


>>>>>>>
>>>>>>>
>>>>>>>> Outstanding!!! Thank yoy!
>>>>>>>> How about
>>>>>>>> counting cells in selected range that are btw 5 to 50 and (-5)
>>>>>>>> to (-50)?
>>>>>>>>
>>>>>>>>
>>>>>>>>

>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>> Hi there Aleks,
>>>>>>>>>
>>>>>>>>> You can use the following ...
>>>>>>>>>
>>>>>>>>> =SUMPRODUCT(--(ABS(U2:U2000)>50))
>>>>>>>>>
>>>>>>>>> HTH
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> Regards,
>>>>>>>>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>>>>>>>> To email, remove the NO SPAM. Please keep correspondence to
>>>>>>>>> the board,
>>>>>>>>> as
>>>>>>>>> to benefit others.
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>


>>>>>>>>>
>>>>>>>>>
>>>>>>>>>> I am trying to count cells in selected range that are above 50
>>>>>>>>>> and
>>>>>>>>>> below -50.
>>>>>>>>>> How can I do this without creating another row with absolute
>>>>>>>>>> values. I
>>>>>>>>>> tried this but it doesn't work
>>>>>>>>>>
>>>>>>>>>> =countif(abs(u2:u2000),">50").
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>
>>>>>
 
 
Aleks





PostPosted: Wed Oct 26 20:47:02 CDT 2005 Top

Excel Misc >> absolute function - range Huge thanks! I do some homework :)



> Sorry, typo
> that should be
> =SUMPRODUCT(--(A2:A2000="yes"),U2:U2000)
>
> Regards
>
> Roger Govier
>
>

> > Hi Aleks
> >
> > Try
> > =SUMPRODUCT(--(A2:A2000)="yes"),U2:U2000)
> >
> > You need to read up on how Sumproduct works, then you can do any of
> > these tasks very easily. A good starting point is
> > http://xldynamic.com/source/xld.SUMPRODUCT.html
> >
> > Regards
> >
> > Roger Govier
> >
> >

> >
> >> This is beautiful!!!
> >>
> >> How about the same as below but sum up only values that have "yes"
> >> a2:a2000
> >>

> >>
> >>
> >>> Hi Aleks
> >>>
> >>> Just add another range to Zack's original formula
> >>> =SUMPRODUCT(--(ABS(U2:U2000)>50),U2:U2000)
> >>>
> >>> Regards
> >>>
> >>> Roger Govier
> >>>
> >>>

> >>>
> >>>> :)
> >>>>
> >>>> so here is another one ...
> >>>> I still have the same list u2:u2000 and there are both positive and
> >>>> negative numbers. I need to get a sum of all number >50 and <-50.
> >>>> again, I bow to Zack.
> >>>>

> >>>>
> >>>>
> >>>>
> >>>>> I've been trying to tell him that for a year. Here's Zack!
> >>>>> http://www.vbaexpress.com/forum/member.php?u=11
> >>>>> ************
> >>>>> Anne Troy
> >>>>> www.OfficeArticles.com
> >>>>>


> >>>>>
> >>>>>
> >>>>>> This is awesome! Zack, whoever you are, you're a GOD!
> >>>>>>

> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>> Possibly ..
> >>>>>>>
> >>>>>>> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
> >>>>>>>
> >>>>>>> Note that this will not count those equal to 50.
> >>>>>>>
> >>>>>>> HTH
> >>>>>>>
> >>>>>>> --
> >>>>>>> Regards,
> >>>>>>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>>>>>> To email, remove the NO SPAM. Please keep correspondence to the
> >>>>>>> board, as
> >>>>>>> to benefit others.
> >>>>>>>
> >>>>>>>
> >>>>>>>


> >>>>>>>
> >>>>>>>
> >>>>>>>> Outstanding!!! Thank yoy!
> >>>>>>>> How about
> >>>>>>>> counting cells in selected range that are btw 5 to 50 and (-5)
> >>>>>>>> to (-50)?
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>

> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>> Hi there Aleks,
> >>>>>>>>>
> >>>>>>>>> You can use the following ...
> >>>>>>>>>
> >>>>>>>>> =SUMPRODUCT(--(ABS(U2:U2000)>50))
> >>>>>>>>>
> >>>>>>>>> HTH
> >>>>>>>>>
> >>>>>>>>> --
> >>>>>>>>> Regards,
> >>>>>>>>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
> >>>>>>>>> To email, remove the NO SPAM. Please keep correspondence to
> >>>>>>>>> the board,
> >>>>>>>>> as
> >>>>>>>>> to benefit others.
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>


> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>> I am trying to count cells in selected range that are above 50
> >>>>>>>>>> and
> >>>>>>>>>> below -50.
> >>>>>>>>>> How can I do this without creating another row with absolute
> >>>>>>>>>> values. I
> >>>>>>>>>> tried this but it doesn't work
> >>>>>>>>>>
> >>>>>>>>>> =countif(abs(u2:u2000),">50").
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>>>
> >>>>>>>
> >>>>>
>
 
 
Zack





PostPosted: Thu Oct 27 12:08:21 CDT 2005 Top

Excel Misc >> absolute function - range ROFL!! Thanks for the accolades Anne. You always were (and still are) my
biggest supporter. ;)

Aleks, I have a written paper that may help with some understanding to
what's going on in these formulas. I like it best for the links it
contains, but others have said they enjoyed it. It only nips the tip of the
iceberg, but may help ...

http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=42
--
Regards,
Zack Barresse, aka firefytr, (GT = TFS FF Zack)
To email, remove the NO SPAM. Please keep correspondence to the board, as
to benefit others.





> I've been trying to tell him that for a year. Here's Zack!
> http://www.vbaexpress.com/forum/member.php?u=11
> ************
> Anne Troy
> www.OfficeArticles.com
>


>> This is awesome! Zack, whoever you are, you're a GOD!
>>

>>
>>> Possibly ..
>>>
>>> =SUMPRODUCT(--(ABS(N2:N2000)>5),--(ABS(N2:N2000)>50))
>>>
>>> Note that this will not count those equal to 50.
>>>
>>> HTH
>>>
>>> --
>>> Regards,
>>> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>> To email, remove the NO SPAM. Please keep correspondence to the board,
>>> as
>>> to benefit others.
>>>
>>>
>>>


>>> > Outstanding!!! Thank yoy!
>>> > How about
>>> > counting cells in selected range that are btw 5 to 50 and (-5) to
>>> > (-50)?
>>> >
>>> >
>>> >

>>> >
>>> >> Hi there Aleks,
>>> >>
>>> >> You can use the following ...
>>> >>
>>> >> =SUMPRODUCT(--(ABS(U2:U2000)>50))
>>> >>
>>> >> HTH
>>> >>
>>> >> --
>>> >> Regards,
>>> >> Zack Barresse, aka firefytr, (GT = TFS FF Zack)
>>> >> To email, remove the NO SPAM. Please keep correspondence to the
>>> >> board,
>>> >> as
>>> >> to benefit others.
>>> >>
>>> >>
>>> >>
>>> >>


>>> >> >I am trying to count cells in selected range that are above 50 and
>>> >> >below -50.
>>> >> > How can I do this without creating another row with absolute
>>> >> > values.
>>> >> > I
>>> >> > tried this but it doesn't work
>>> >> >
>>> >> > =countif(abs(u2:u2000),">50").
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
>
>