IF FUNCTION HELP

[Simon Lee]

Hi there

I'm sure this is child's play but I need the formula for this instance:

In the Cell J1 I need the following to happen;

If the letter Y is present in Cell D1 then I need the numerical values in Cells E1, F1, G1 & H1 to be added and then appear in Cell J1.

If the letter N is present in Cell D1 then I need a Zero to appear in Cell J1.

Try as I might I can't find a similar example.

Help

 

[anupamtiwari05]

Hi Simon,

Welcome to Chandoo.org Forum,

Please enter the below formula in Cell J1 and drag it down till the last row of your data.

=IF($D1="Y",$E1&$F1&$G1&$H1,IF($D1="N",0))

Please let me know if you find problem in the above formula.

Thanks & Regards,
Anupam

 

[Sajan]

Hello Simon,
Not sure what you mean by "letter Y is present in cell D1"... if cell D1 can contain a string that may include the letter Y (such as "HAPPY") then, you would want to replace the $D1="Y" with FIND("Y",$D1)

Also, not sure if you wanted the numbers in E1:H1 concatenated or added up. If you want the numbers added, use SUM(E1:H1)

Cheers,
Sajan.

 

[James Perry]

Cool solution Anupam.
But Simon used the word "added".
So the "&" symbol in ur formula should be replaced with "+"
Cheers!

 

[Xiq]

Also,
What should happen when lowercase (i.g.: "y" and "n") letters are used? Or more then just Y and N?

Here is my shot at it:

=SUM(--ISNUMBER(FIND({"y";"Y"},D1)))*SUM($E1:$H1)

• It looks for the letters "y" or "Y" in D1 and sums up E1:H1
• If neither, then zero

Or here is one if you only could have Y or N in D1:

=(D1="Y")*SUM(E1:H1)

 

[Simon Lee]

Thanks all of you.....Anupam spot on first time....many thanks for the responses, much appreciated.

 

[Simon Lee]

Ok got another one for you guys.....(and ladies of course ).

I have a blank cell and I need the formula to put in it that gives me the result of the following query.

Cell A1 is a Y or N value. Cell A2 is a 1 or 0 value. Cell A3 is the Cell I need the result in.

If A1 = Y and A2 = 1 then A3 = 0.5, however If A1 = Y and A2 = 0 then A3=0 as well as if A1 = N and A2 = 1 then A3 = 1, however if A1 = N and A2 = 0 then A3 = 0

 

[Abhijeet R. Joshi]

Hi Simon,

I think you will need to open a new thread for this..
However, just my two cents:
=IF(A1&A2="Y1",0.5,IF(OR(A1&A2="N0",A1&A2="Y0"),0,IF(A1&A2="N1",1,"No Match")))

Hope this helps...

 

[Simon Lee]

Thanks to Abhijeet R. Joshi got it in one!

the solution was..... =IF(A1&A2="Y1",0.5,IF(OR(A1&A2="N0",A1&A2="Y0"),0,IF(A1&A2="N1",1,"No Match")))

Perfect!