countif function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

renee - 21 Sep 2006 20:19 GMT

I want Excel to only count cells with < 3 value. Here is what I used, but
this is wrong. Can anyone tell me what I am doing wrong?
=COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
Renee

Reply to this Message

Bob Phillips - 21 Sep 2006 20:22 GMT

=COUNTIF(('Monitor Tool'!N11:N26,"<"&Data!C1)

COUNTIF only needs a range and a condition

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I want Excel to only count cells with < 3 value. Here is what I used, but
> this is wrong. Can anyone tell me what I am doing wrong?
> =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> Renee

Reply to this Message

renee - 21 Sep 2006 21:55 GMT

I have two ranges, 'Monitor Tool'!N11:N26,'Monitor Tool'!N20:26. I cannot
seem to get the function to work with both of them. It works fine with just
one. Any suggestions?

> =COUNTIF(('Monitor Tool'!N11:N26,"<"&Data!C1)
>
[quoted text clipped - 4 lines]
> > =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> > Renee

Reply to this Message

Bob Phillips - 21 Sep 2006 22:32 GMT

Do you want to sum one if the other meets the condition? If so, they must be
the same size. If not, what exactly do you want to do?

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I have two ranges, 'Monitor Tool'!N11:N26,'Monitor Tool'!N20:26. I cannot
> seem to get the function to work with both of them. It works fine with just
[quoted text clipped - 8 lines]
> > > =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> > > Renee

Reply to this Message

renee - 22 Sep 2006 15:40 GMT

No I do not want it to sum. I want both ranges to count the cells with the
#3 (Data!C1) in them.

> Do you want to sum one if the other meets the condition? If so, they must be
> the same size. If not, what exactly do you want to do?
[quoted text clipped - 13 lines]
> > > > =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> > > > Renee

Reply to this Message

RagDyeR - 22 Sep 2006 16:59 GMT

Do you have a typo?
What are your ranges ... exactly?

Your OP formula depicted *overlapping* ranges of
N11:N26
and
N20:26 (?!?!)

So, now you've stated that you want to count cells in 2 ranges that contain
values less then the value in C1.

Fine ... what are your actual ranges?

You could simply combine 2 Countif() functions:

=Countif(range1,"<"&C1)+Countif(range2,"<"&C1)

Signature

HTH,

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

No I do not want it to sum. I want both ranges to count the cells with the
#3 (Data!C1) in them.

"Bob Phillips" wrote:

> Do you want to sum one if the other meets the condition? If so, they must be
> the same size. If not, what exactly do you want to do?
[quoted text clipped - 15 lines]
> > > > =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> > > > Renee

Reply to this Message

renee - 22 Sep 2006 17:44 GMT

I got it. Thank you RD

> Do you have a typo?
> What are your ranges ... exactly?
[quoted text clipped - 39 lines]
> > > > > =COUNTIF(('Monitor Tool'!N11:N26,'Monitor Tool'!N20:26)"<" &Data!C1)
> > > > > Renee

Reply to this Message

RagDyer - 22 Sep 2006 18:38 GMT

You're welcome, and appreciate the feed-back.

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>I got it. Thank you RD
>
[quoted text clipped - 46 lines]
>> > > > > &Data!C1)
>> > > > > Renee

Reply to this Message