using countif without a range

hawley · Apr 8th, 2002, 10:15 AM

I am trying to use a countif statement but I want don't want to look at a whole range just certain cells. I have tried different things but I keep getting an error. Here is something like I am trying.

I just want to look at cells T17, T20 and T30 but nothing in between.
=IF(COUNTIF((T17,T20,T30),"X")>0,"X","no")

If you have any suggestions let me know.
Thanks-

Mark W. · Apr 8th, 2002, 10:19 AM

The 1st argument of COUNTIF must be a cell range. A cell range is defined as...

"To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (

, and then the reference to the cell in the lower-right corner of the range."

Al Chara · Apr 8th, 2002, 10:20 AM

Try the following:

=IF(SUMPRODUCT((T17>0)*(T20>0)*(T30>0))=1,"X","no")

Aladin Akyurek · Apr 8th, 2002, 10:36 AM

Quote:

On 2002-04-08 09:15, hawley wrote:
I am trying to use a countif statement but I want don't want to look at a whole range just certain cells. I have tried different things but I keep getting an error. Here is something like I am trying.

I just want to look at cells T17, T20 and T30 but nothing in between.
=IF(COUNTIF((T17,T20,T30),"X")>0,"X","no")

If you have any suggestions let me know.
Thanks-

It seems just an "X" in one the target cells is sufficient, to return as result an "X", otherwise a "no".

=IF(SUMPRODUCT((T17="X")+(T20="X")+(T30="X"))>0,"X","no")

would do that job.

Mark W. · Apr 8th, 2002, 10:38 AM

Quote:

On 2002-04-08 09:36, Aladin Akyurek wrote:

Quote:

On 2002-04-08 09:15, hawley wrote:
I am trying to use a countif statement but I want don't want to look at a whole range just certain cells. I have tried different things but I keep getting an error. Here is something like I am trying.

I just want to look at cells T17, T20 and T30 but nothing in between.
=IF(COUNTIF((T17,T20,T30),"X")>0,"X","no")

If you have any suggestions let me know.
Thanks-

It seems just an "X" in one the target cells is sufficient, to return as result an "X", otherwise a "no".

=IF(SUMPRODUCT((T17="X")+(T20="X")+(T30="X"))>0,"X","no")

would do that job.

To me it looks like hawley wants to evalute a 4 element set consisting of {T17,T20,T30,"X"}.

Al Chara · Apr 8th, 2002, 10:49 AM

Oops, I missed the bit about X being in there.

=IF(SUMPRODUCT((T17="X")*(T20="X")*(T30="X"))=1,"X","no")

[ This Message was edited by: Al Chara on 2002-04-08 09:54 ]

Aladin Akyurek · Apr 8th, 2002, 11:08 AM

Quote:

On 2002-04-08 09:38, Mark W. wrote:

Quote:

On 2002-04-08 09:36, Aladin Akyurek wrote:

Quote:

On 2002-04-08 09:15, hawley wrote:
I am trying to use a countif statement but I want don't want to look at a whole range just certain cells. I have tried different things but I keep getting an error. Here is something like I am trying.

I just want to look at cells T17, T20 and T30 but nothing in between.
=IF(COUNTIF((T17,T20,T30),"X")>0,"X","no")

If you have any suggestions let me know.
Thanks-

It seems just an "X" in one the target cells is sufficient, to return as result an "X", otherwise a "no".

=IF(SUMPRODUCT((T17="X")+(T20="X")+(T30="X"))>0,"X","no")

would do that job.

To me it looks like hawley wants to evalute a 4 element set consisting of {T17,T20,T30,"X"}.

If so, I'll try to cope.

hawley · Apr 8th, 2002, 12:10 PM

Thanks for the help everyone. What I ended up using was this
IF(SUMPRODUCT((T10="X")+(T20="X")+(T30="X"))>0,"X","no")

I also found this will work but the above is less typing.
IF((COUNTIF(T10,"X")+COUNTIF(T20,"X")+COUNTIF(T30,"X"))>0,"X","no")

Apr 8th, 2002, 10:19 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	The 1st argument of COUNTIF must be a cell range. A cell range is defined as... "To refer to a range of cells, enter the reference for the cell in the upper-left corner of the range, a colon (, and then the reference to the cell in the lower-right corner of the range."

Apr 8th, 2002, 10:20 AM	#3
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Try the following: =IF(SUMPRODUCT((T17>0)(T20>0)(T30>0))=1,"X","no") __________________ Kind regards, Al Chara

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 10:15 AM	#1
hawley Board Regular Join Date: Apr 2002 Posts: 169	I am trying to use a countif statement but I want don't want to look at a whole range just certain cells. I have tried different things but I keep getting an error. Here is something like I am trying. I just want to look at cells T17, T20 and T30 but nothing in between. =IF(COUNTIF((T17,T20,T30),"X")>0,"X","no") If you have any suggestions let me know. Thanks-

Apr 8th, 2002, 10:49 AM	#6
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Oops, I missed the bit about X being in there. =IF(SUMPRODUCT((T17="X")(T20="X")(T30="X"))=1,"X","no") [ This Message was edited by: Al Chara on 2002-04-08 09:54 ]

Apr 8th, 2002, 12:10 PM	#8
hawley Board Regular Join Date: Apr 2002 Posts: 169	Thanks for the help everyone. What I ended up using was this IF(SUMPRODUCT((T10="X")+(T20="X")+(T30="X"))>0,"X","no") I also found this will work but the above is less typing. IF((COUNTIF(T10,"X")+COUNTIF(T20,"X")+COUNTIF(T30,"X"))>0,"X","no")