Need help with IF statement

[leopardhawk]

Cell C1 has the name 'Bob' in it and cell D1 has the name 'Sue' in it (without quotes).

The formula below is returning 'Bob combined net income (before retirement)' just as it should and if C1 is blank, it is returning 'Name & Spouse/Partner combined net income (before retirement)'. This is all good.

I need help to modify the formula so that it returns both names, Bob & Sue, so that the result would be 'Bob & Sue combined net income (before retirement)'. I keep running into errors, maybe from the ampersand between the two names? Or from where I am placing the double-quotes? I would prefer to have the ampersand between the two names if possible. Could sure use some help.

=IF(C1=0,"Name & Spouse/Partner combined net income (before retirement)",C1&" combined net income (before retirement)")

Appreciate any advice.

 

[Aryatect]

Hi Leopardhawk,

if you have office 365 or latest Excel 2016 you can use the below formula:

=TEXTJOIN(" & ",TRUE,C1:D1)&" combined net income (before retirement)"

Cell C1 has the name 'Bob' in it and cell D1 has the name 'Sue' in it (without quotes).

The formula below is returning 'Bob combined net income (before retirement)' just as it should and if C1 is blank, it is returning 'Name & Spouse/Partner combined net income (before retirement)'. This is all good.

I need help to modify the formula so that it returns both names, Bob & Sue, so that the result would be 'Bob & Sue combined net income (before retirement)'. I keep running into errors, maybe from the ampersand between the two names? Or from where I am placing the double-quotes? I would prefer to have the ampersand between the two names if possible. Could sure use some help.


Appreciate any advice.

 

[leopardhawk]

Thanks for reaching out. I am running Excel 2016 and your formula is returning a #NAME error. I still need the IF statement for if/when C1 is blank so I appended your formula to the [value_if_false] part of my IF statement and I am receiving the same #NAME error.

 

[Aryatect]

Try this one:

=IF(C1="","Name & Spouse/Partner combined net income (before retirement)",IF(D1<>"",C1& " & "&D1&" combined net income (before retirement)",C1& " combined net income (before retirement)"))

Thanks for reaching out. I am running Excel 2016 and your formula is returning a #NAME error. I still need the IF statement for if/when C1 is blank so I appended your formula to the [value_if_false] part of my IF statement and I am receiving the same #NAME error.

 

[leopardhawk]

Fantastic!!! Thank you so much! This works perfectly.

Cheers!