If to apply to a range

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

Hello.
I'd like to apply a formula to show a particular text is present in a range
of cells.
I can apply the following formula and it works for one cell
=IF(D2="A","Yes","No")
But it doesn't work if I type D2:N2 to make it apply to the row of data that
I have.
Can any one please tell me what the correct format is?
Thanks
 
Are you looking for at least one "A" in that range?
=if(countif(d2:n2,"A")>0,"Yes","No")

or if you're looking for all those cells to equal A:
=if(countif(d2:n2,"A")=11,"Yes","No")
 
Here's two:

=IF(COUNTIF(D2:N2,"A")>0,"Yes","No")

or:

=IF(ISNA(MATCH("A",D2:N2,0)),"No","Yes")

Hope this helps.

Pete
 
Thank you Dave
Yes I want to find any number of cells that contain A for the result to be
"yes".

I was working towards trying to find a way to put the formula in Conditional
formatting so that if there is one or more "A" entered in the range, then
the whole range gets a colour. Is that possible?

Thank you
 
put the formula in Conditional formatting

Select the range D2:N2
Goto Format>Conditional Formatting
Formula Is: =COUNTIF($D2:$N2,"A")
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP


JB said:
Thank you Dave
Yes I want to find any number of cells that contain A for the result to
be "yes".

I was working towards trying to find a way to put the formula in
Conditional formatting so that if there is one or more "A" entered in the
range, then the whole range gets a colour. Is that possible?

Thank you
Click to expand...
 
Back
Top