I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

=SUM(IF(ISTEXT(B6:K300),,IF(ISEMPTY(B6:K300),,IF(A BS(B6:K300)<100,1,))))

This is an array formula therefore should be netered with CTRL+SHIFT
+ENTER

=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

--
__________________________________
HTH

Bob

"Steven" wrote in message
...
I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

=SUMPRODUCT(--(IF(ISERROR(VALUE(B6:C9)),0,ABS(B6:C9)<100)))
as an array formula (Ctrl+Shift+Enter)

Regards,
Stefi

€žSteven€� ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


€žSteven€� ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt írta:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

€žBob Phillips€� ezt Ã*rta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

I see why you did it now (I must admit to wondering about that), but I would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

"Bob Phillips" ezt írta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt írta:

I have numbers in range B6:K300 and also some text. I want to count
the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

=SUM(--if(ISNUMBER(B6:K300);ABS(B6:K300)<100))
This is an array formula therefore should be netered with CTRL+SHIFT
+ENTER

Pedro J.

I see why you did it now (I must admit to wondering about that), but I would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

I tried it and it worked with SUM function but not simply, only when entered
as an array formula.
Stefi


€žBob Phillips€� ezt Ã*rta:

I see why you did it now (I must admit to wondering about that), but I would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

"Bob Phillips" ezt Ã*rta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to count
the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Oh yeah, I didn't mention array-entered again as we had already established
that.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
I tried it and it worked with SUM function but not simply, only when
entered
as an array formula.
Stefi


"Bob Phillips" ezt írta:

I see why you did it now (I must admit to wondering about that), but I
would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

"Bob Phillips" ezt írta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt írta:

I have numbers in range B6:K300 and also some text. I want to
count
the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Try this:
None "Array" enter

=SUM(COUNTIF(B6:K300,{"-100","=100"})*{1,-1})


"Steven" wrote:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Bob,

Your formula is almost getting me there. Except if I put text in a cell it
gives me #VALUE!

Thank you,


Steven



"Bob Phillips" wrote:

Oh yeah, I didn't mention array-entered again as we had already established
that.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
I tried it and it worked with SUM function but not simply, only when
entered
as an array formula.
Stefi


"Bob Phillips" ezt Ã*rta:

I see why you did it now (I must admit to wondering about that), but I
would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

"Bob Phillips" ezt Ã*rta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to
count
the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Now I see your later response. And that one works great. Thank you.

"Bob Phillips" wrote:

I see why you did it now (I must admit to wondering about that), but I would
use ISNUMBER, then it simply becomes

=SUM(IF(ISNUMBER(B6:C9),IF(ABS(B6:C9)<100,1)))

the intent is nice and clear then.

--
__________________________________
HTH

Bob

"Stefi" wrote in message
...
Thanks Bob, now I see your point!

I think that we must use an array formula. I tried your normal formula
=SUMPRODUCT(--(B6:K300<""),--(ABS(B6:K300)<100))

but it returned #VALUE error because of not handling text cells.

Stefi

"Bob Phillips" ezt Ã*rta:

If you are going to use an array formula, you might as well use SUM as
SUMPRODUCT

--
HTH

Bob

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

"Stefi" wrote in message
...
Sorry, I forgot to mention that replace my test range with B6:K300
Stefi


"Steven" ezt Ã*rta:

I have numbers in range B6:K300 and also some text. I want to count
the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Wow Steven, those are complicated answers, how about just using:

=COUNTIF(B6:K300,"< 100")

you don't need to worry about absolute values as an absolute value only
drops the negative from an number below 0. Your looking for a value under
100, any value below 0 is still below 100. In fact, using an absolute will
probably not give you the correct answer if you deal with negative numbers as
-101 is below 100, abs(-101) is above 100. IE: it equals 101

"Steven" wrote:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

But he probably wants -101 to be excluded.

--
__________________________________
HTH

Bob

"TWhizTom" wrote in message
...
Wow Steven, those are complicated answers, how about just using:

=COUNTIF(B6:K300,"< 100")

you don't need to worry about absolute values as an absolute value only
drops the negative from an number below 0. Your looking for a value under
100, any value below 0 is still below 100. In fact, using an absolute will
probably not give you the correct answer if you deal with negative numbers
as
-101 is below 100, abs(-101) is above 100. IE: it equals 101

"Steven" wrote:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven

Very nice, I'm working hard in order to understand it. Could you give me some
guide to it?
Thanks,
Stefi


€žTeethless mama€� ezt Ã*rta:

Try this:
None "Array" enter

=SUM(COUNTIF(B6:K300,{"-100","=100"})*{1,-1})


"Steven" wrote:

I have numbers in range B6:K300 and also some text. I want to count the
number of occurances of cells with an absolute value of < 100.

Thank you,

Steven