• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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 :)
 
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
 
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.
 
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)
 
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
 
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...:)
 
Back
Top