Forum Discussion

Copper Contributor

Sep 22, 2020

Solved

Greetings, I hope you are well. I just need some formula help.

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 returnin...

excel

Formulas and Functions

Rajesh_Sinha

Sep 23, 2020

UKBlueSteel ,,,

For Row has Y & N:

=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.

To check the products:

=IF(OR(A1={"Steel","Cooper","Nickel"}),"Y","N")

For Ni and others:

=IF(OR(A1={"St","Co","Ni"}),"Y","N")

UKBlueSteel

Copper Contributor

Sep 23, 2020

Very interesting, thanks Rajesh. I learnt a lot from your post, very invaluable information.

I did wonder about case and non case sensitive functions.

Rajesh_Sinha

Steel Contributor

Sep 24, 2020

Glad to help you,,, in case you are satisfied with my post then you may mark it as best answer/post. Keep asking ☺

UKBlueSteel
Copper Contributor
Sep 29, 2020
Hi RajeshRajesh_Sinha

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 🙂
- Rajesh_Sinha
  Steel Contributor
  Sep 30, 2020
  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.
  - UKBlueSteel
    Copper Contributor
    Sep 30, 2020
    Thank you so much for this! I wonder do you know where I could learn more about these advanced functions? Like an online course or to get MS Excel certification? Thank you Rajesh