Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Aleks
 
Posts: n/a
Default 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").
  #2   Report Post  
Elkar
 
Posts: n/a
Default absolute function - range

Try this:

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


"Aleks" wrote:

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").

  #3   Report Post  
Zack Barresse
 
Posts: n/a
Default 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.




"Aleks" wrote in message
...
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").



  #4   Report Post  
Aleks
 
Posts: n/a
Default absolute function - range

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



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").




  #5   Report Post  
Zack Barresse
 
Posts: n/a
Default 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.



"Aleks" wrote in message
...
Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to (-50)?



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").








  #6   Report Post  
Aleks
 
Posts: n/a
Default absolute function - range

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

"Zack Barresse" wrote:

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.



"Aleks" wrote in message
...
Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to (-50)?



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").






  #7   Report Post  
Anne Troy
 
Posts: n/a
Default 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

"Aleks" wrote in message
...
This is awesome! Zack, whoever you are, you're a GOD!

"Zack Barresse" wrote:

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.



"Aleks" wrote in message
...
Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to
(-50)?



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").








  #8   Report Post  
Aleks
 
Posts: n/a
Default 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.

"Anne Troy" wrote:

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

"Aleks" wrote in message
...
This is awesome! Zack, whoever you are, you're a GOD!

"Zack Barresse" wrote:

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.



"Aleks" wrote in message
...
Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to
(-50)?



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").









  #9   Report Post  
Roger Govier
 
Posts: n/a
Default absolute function - range

Hi Aleks

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

Regards

Roger Govier


Aleks wrote:
:)

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.

"Anne Troy" wrote:


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

"Aleks" wrote in message
...

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

"Zack Barresse" wrote:


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.



"Aleks" wrote in message
...

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



"Zack Barresse" wrote:


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.




"Aleks" wrote in message
...

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").







  #10   Report Post  
Aleks
 
Posts: n/a
Default absolute function - range

This is beautiful!!!

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

"Roger Govier" wrote:

Hi Aleks

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

Regards

Roger Govier


Aleks wrote:
:)

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.

"Anne Troy" wrote:


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

"Aleks" wrote in message
...

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

"Zack Barresse" wrote:


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.



"Aleks" wrote in message
...

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



"Zack Barresse" wrote:


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.




"Aleks" wrote in message
...

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").










  #11   Report Post  
Roger Govier
 
Posts: n/a
Default 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


Aleks wrote:
This is beautiful!!!

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

"Roger Govier" wrote:


Hi Aleks

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

Regards

Roger Govier


Aleks wrote:

:)

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.

"Anne Troy" wrote:



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

"Aleks" wrote in message
...


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

"Zack Barresse" wrote:



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.



"Aleks" wrote in message
...


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



"Zack Barresse" wrote:



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.




"Aleks" wrote in message
...


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").





  #12   Report Post  
Roger Govier
 
Posts: n/a
Default absolute function - range

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

Regards

Roger Govier


Roger Govier wrote:
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


Aleks wrote:

This is beautiful!!!

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

"Roger Govier" wrote:


Hi Aleks

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

Regards

Roger Govier


Aleks wrote:

:)

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.

"Anne Troy" wrote:



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

"Aleks" wrote in message
...


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

"Zack Barresse" wrote:



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.



"Aleks" wrote in message
...


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



"Zack Barresse" wrote:



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.




"Aleks" wrote in message
...


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").






  #13   Report Post  
Aleks
 
Posts: n/a
Default absolute function - range

Huge thanks! I do some homework :)

"Roger Govier" wrote:

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

Regards

Roger Govier


Roger Govier wrote:
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


Aleks wrote:

This is beautiful!!!

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

"Roger Govier" wrote:


Hi Aleks

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

Regards

Roger Govier


Aleks wrote:

:)

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.

"Anne Troy" wrote:



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

"Aleks" wrote in message
...


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

"Zack Barresse" wrote:



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.



"Aleks" wrote in message
...


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



"Zack Barresse" wrote:



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.




"Aleks" wrote in message
...


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").







  #14   Report Post  
Zack Barresse
 
Posts: n/a
Default 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/arti...ticle&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.



"Anne Troy" wrote in message
...
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

"Aleks" wrote in message
...
This is awesome! Zack, whoever you are, you're a GOD!

"Zack Barresse" wrote:

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.



"Aleks" wrote in message
...
Outstanding!!! Thank yoy!
How about
counting cells in selected range that are btw 5 to 50 and (-5) to
(-50)?



"Zack Barresse" wrote:

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.




"Aleks" wrote in message
...
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").










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
"COUNTU" function in Excel to count unique entries in a range WayneL Excel Worksheet Functions 20 September 19th 08 03:50 AM
creating function (vba) with range arguments Fredouille Excel Worksheet Functions 2 September 12th 05 11:01 AM
Number range function Sonya T Excel Discussion (Misc queries) 2 July 19th 05 01:19 AM
Function that filters a list (Database) for criteria in a range a. FirstVette52 Excel Worksheet Functions 0 February 8th 05 05:37 PM
Formula to list unique values JaneC Excel Worksheet Functions 4 December 10th 04 01:25 AM


All times are GMT +1. The time now is 09:42 AM.

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"