absolute function - range

A

Aleks

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

Zack Barresse

Hi there Aleks,

You can use the following ...

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

HTH
 
A

Aleks

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

Zack Barresse

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.
 
A

Aleks

:)

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.
 
R

Roger Govier

Hi Aleks

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

Regards

Roger Govier
 
A

Aleks

This is beautiful!!!

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

Roger Govier

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

Regards

Roger Govier
 
Z

Zack Barresse

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.
 
Top