09-22-2020 08:19 AM
I have several cells in a row that have Y or N in them, I want to enter a formula in a column for when one of those cells in a row is "Y" to return a "Y" value.
Also I have in those cells returning a "Y" value if say "Steel" or "Copper" is in a product name in column A. I wondered how to create a formula that gives a "Y" value if a cell name contains "Steel" or "Copper" or "Nickel".
Thirdly, how can I return a "Y" value for a product name in column A having "Ni" or "Cu" in the name on its own without returning a false positive for say "Continiti" or "Occupose" etc. but for say "Product C Ni Cu Plate"
Many Thanks
09-22-2020 02:30 PM
HI @UKBlueSteel
It would be great if you can attach sample file along with your desired output enter manually
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more
09-22-2020 03:29 PM
As variants
1) for columns from A to C
=IF(SUMPRODUCT(--(A1:C1="Y")),"Y","N")
2) For A1
=IF(SUM(COUNTIF(A1,"*"&{"Steel","Cooper","Nickel"}&"*")),"Y","N")
3) same as above if only take " Ni " and " Cu ". If spaces around is not the case, when it shall be another formal logic.
09-23-2020 01:23 AM
09-23-2020 01:52 AM
Solution@UKBlueSteel ,,,
=IF(COUNTIF($A$2:$E$2,"Y")>0,"Y","N")
Or to make the formula dyanamic,, you can use this even:
=IF(COUNTIF($A$2:$E$2,A1)>0,"Y","N")
Where A1 has Y, or you may put other alphaet to test.
=IF(OR(A1={"Steel","Cooper","Nickel"}),"Y","N")
For Ni and others:
=IF(OR(A1={"St","Co","Ni"}),"Y","N")
09-23-2020 02:22 AM
@UKBlueSteel ,,,
Let me point out the mistake in the formula you have used,
=IF(IS NUMBER(SEARCH("Nickel",A2)),"Y","N")
Space is not allowed between IS & Number,,, since ISNUMBER is reserve word or a Function.
So use it as,,
=IF(ISNUMBER(SEARCH("Nickel",A2)),"Y","N") , will works.
Not let me say about combination of ISNUMBER & SEARCH.
Note: In your case since formula tests more than one product at a time, so looks useless, but if you alter the formula will work.
=IF(ISNUMBER(SEARCH(E1,A1)),"Y","N")
09-23-2020 02:50 AM
09-24-2020 12:59 AM
09-29-2020 05:42 AM
Hi Rajesh@Rajesh-S
I was wondering if I had several columns with different metals in that has "Y" in, say
Columns F2-I2 and I wanted a formulat for column E that gives a Y if there is just one Y in any of those columns but if there is a N in column J2 it gives a "N" that trumps any "Y" value?
Thank You
09-30-2020 03:50 AM
@UKBlueSteel ,,,
I'm sure that you are looking for this:
=IF($J2="N","N",IF(COUNTIF($F2:$I2,"Y")=1,"Y","No Match"))
You may adjust cell references in the formula as needed.
09-30-2020 04:29 AM
09-30-2020 05:47 AM
09-30-2020 11:37 PM
10-01-2020 06:38 AM
10-01-2020 10:18 PM
10-09-2020 03:07 AM
10-09-2020 04:53 AM - edited 10-09-2020 05:02 AM
You need an array (CSE) formula :
{=IF(SUM(COUNTIF($G$5:$G$8,"*"&G4&"*")),"Y","N")}
Or you can use this one too:
{=IF(SUM(COUNTIF($G$5:$G$8,G4)),"Y","N")}
Because "*"&G4&"*" works as partial match, (wild card).
N.B.
{=IF(SUM(COUNTIF($G$5:$G$8,"*"&G4&"*"))>1,"Y","N")}
=IF((COUNTIF($G$5:$G$8,G4)),"Y","N")
Adjust cell references in the formula as needed.
10-21-2020 06:47 AM
10-22-2020 03:31 AM