I want to check if a known number appears in a column of cells. If it does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number "1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

=IF(COUNTIF(F2:F8),1)0,1,0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Iain - T" <Iain - wrote in message
...
I want to check if a known number appears in a column of cells. If it
does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number
"1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

Thanks Bob,

I tried but still does not work.......... any other ideas?

"Bob Phillips" wrote:

=IF(COUNTIF(F2:F8),1)0,1,0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Iain - T" <Iain - wrote in message
...
I want to check if a known number appears in a column of cells. If it
does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number
"1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

Just another option...

=SIGN(COUNTIF(F2:F8,1))

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Iain - T" wrote in message
...
Thanks Bob,

I tried but still does not work.......... any other ideas?

"Bob Phillips" wrote:

=IF(COUNTIF(F2:F8),1)0,1,0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Iain - T" <Iain - wrote in message
...
I want to check if a known number appears in a column of cells. If it
does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0),
where
cells F2 to F8 contain various numbers to be checked against the number
"1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

Is this what you're looking for?

=--(SUMPRODUCT((F2:F8=1)+(F2:F8=D23))0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Iain - T" wrote:

I want to check if a known number appears in a column of cells. If it does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number "1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it

Bob's formula was correct but had an extra bracket that caused an error

Instead of:
=IF(COUNTIF(F2:F8),1)0,1,0)

Use:
=IF(COUNTIF(F2:F8,1)0,1,0)


--
Cutter
------------------------------------------------------------------------
Cutter's Profile: http://www.excelforum.com/member.php...fo&userid=9848
View this thread: http://www.excelforum.com/showthread...hreadid=505692

Thanks Ron,

I would never have got that ....... have plugged in the formula..... Great!

Cheers Mate............. Iain

"Ron Coderre" wrote:

Is this what you're looking for?

=--(SUMPRODUCT((F2:F8=1)+(F2:F8=D23))0)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Iain - T" wrote:

I want to check if a known number appears in a column of cells. If it does
then I want a "1" to appear if not then I want a "0" to appear.

In cell F23 I have used =IF(F2:F8="1",1,0) and =IF(F2:F8=D23,1,0), where
cells F2 to F8 contain various numbers to be checked against the number "1"
or the value of D23 (which in this case equals 1.)

All I get is a #VALUE in the

I would then check F2 to F8 against D24 (=2) or the number 2

I think the answer may be simple, but I just cannot see it