# IF FUNCTION HELP

#### Simon Lee

##### New Member
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

##### Member
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

##### Excel Ninja
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

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

#### Xiq

##### Active Member
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:
Code:
``=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:
Code:
``=(D1="Y")*SUM(E1:H1)``

#### Simon Lee

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

#### Simon Lee

##### New Member
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

##### Active Member
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

##### New Member
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!