# 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 • James Perry

#### 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!
{"ft_lang_label":"__label__en","ft_lang_prob":0.9457833,"math_prob":0.8585398,"size":801,"snap":"2020-34-2020-40","text_gpt3_token_len":218,"char_repetition_ratio":0.17565872,"word_repetition_ratio":0.94186044,"special_character_ratio":0.26841447,"punctuation_ratio":0.07446808,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99517256,"pos_list":[0,1,2,3,4,5,6,7,8],"im_url_duplicate_count":[null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-09-22T19:42:25Z\",\"WARC-Record-ID\":\"<urn:uuid:08cdeb44-a12c-45f0-ab93-f1b09d7811c3>\",\"Content-Length\":\"64157\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:2d7287e3-00e9-466d-a49a-816701d43a41>\",\"WARC-Concurrent-To\":\"<urn:uuid:e84acc47-46a4-4e83-a7c7-1ca524c44073>\",\"WARC-IP-Address\":\"67.222.17.217\",\"WARC-Target-URI\":\"https://chandoo.org/forum/threads/if-function-help.11955/\",\"WARC-Payload-Digest\":\"sha1:PDVESQAYPI4A7O4OS2AH33UR43B27WUG\",\"WARC-Block-Digest\":\"sha1:2WDTMISHESTV73HCVJL4RGESSEYN67RW\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-40/CC-MAIN-2020-40_segments_1600400206763.24_warc_CC-MAIN-20200922192512-20200922222512-00083.warc.gz\"}"}