# Compound IF statement in Excel

I have stared at this problem for over an hour.  F9 contains a name. F10 contains a value.  My current formula that is not working reads:

=IF(\$F\$9<>""&COUNTIF(K35:S35,"*")&"-Year, "&COUNTIF(K35:S35,"*")-1&"-Year and "&COUNTIF(K35:S35,"*")&"-Year/"&COUNTIF(K35:S35,"*")-1&"-Year Average Value of "&\$F\$9&"'s "&TEXT(\$F\$10*100,"0.00")&"% Ownership Interest","You must enter a name in F9")

The COUNT portion of the formula works, but when I add the IF statement in front of the formula to prompt the user to enter a Name in F9, it returns a value saying I must enter a name in F9, although F9 contains a name.
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Excel does not use & to check if multiple values are true. Instead, use And(), like this:

``````=If(And(A1=B1, C1=D1), "value-if-true", "value-if-false")
``````
Mechanical EngineerCommented:
Please explain in English all the possible cases you need to handle, and what the result should be.

FWIW, the & character in Excel means to concatenate (append some text) rather than the equivalent of "and". I suspect that you need to use the AND function to mean that several criteria must be met before returning the "True" branch of the IF function.

Also, the word YEAR is a function that returns the year a date occurs in. Your formula suggests that Year is a named range pointing to a particular cell.

If two or more criteria must be met, you might build your IF formula like:
=IF(AND(\$F\$9<>"", COUNTIF(K35:S35,"*")=Year), result if both criteria are True, result if either or both are False)

You could also help get an answer by posting a sample workbook showing several alternative results that you want the formula to give.
Executive Managing MemberAuthor Commented:
This was the formula that was there and it works perfectly, as far as it goes.

=""&COUNTIF(K35:S35,"*")&"-Year, "&COUNTIF(K35:S35,"*")-1&"-Year and "&COUNTIF(K35:S35,"*")&"-Year/"&COUNTIF(K35:S35,"*")-1&"-Year Average Value of "&\$F\$9&"'s "&TEXT(\$F\$10*100,"0.00")&"% Ownership Interest"

But, if F9, F10 or F8 is missing, I want to merely display a message to prompt the user to enter a name in F9 if missing AND/OR his/her ownership percentage in F10 if missing AND/OR the valuation date in F8 if missing.

The formula appears in cell B127 in the attached file.
Executive Managing MemberAuthor Commented:
Mechanical EngineerCommented:
Try the following in B127:
=IF(COUNTA(F8:F10)=3,""&COUNTIF(K35:S35,"*")&"-Year, "&COUNTIF(K35:S35,"*")-1&"-Year and "&COUNTIF(K35:S35,"*")&"-Year/"&COUNTIF(K35:S35,"*")-1&"-Year Average Value of "&\$F\$9&"'s "&TEXT(\$F\$10*100,"0.00")&"% Ownership Interest",TRIM(IF(F8="","Enter valuation date in F8 ","")&IF(F9="","Enter name in F9 ","")&IF(F10="","Enter ownership percentage in F10","")))

You only get 42 characters for the file name when posting on Experts-Exchange, so the file extension was truncated. Truncation of the file extension means that Excel can't open the file. I believe you posted a .xls file, and renamed it for you.
Executive Managing MemberAuthor Commented:
That worked GREAT!  Thank you so much.  In an attempt to build on your example (and possibly learn something in the process) I am trying to modify B125 in accordance with your syntax, but alas I am getting an error.  My trial modification appears in B127 (with a ' in front of the formula so I could store the formula).  I am also modifying B124 looking for a similar outcome.
FBBJ-for-EE-Submission-Rev2.xls
Mechanical EngineerCommented:
For B125, notice the change I made near the beginning of the formula:
=IF(COUNTA(\$F\$9:\$F\$10)=2,"Multiplied by "&\$F\$9&"'s "&TEXT(\$F\$10*100,"0.00")&"% Ownership Interest ",IF(\$F\$9="","Please enter the Owner/Partner/Shareholder's Name in F9... ","")&IF(\$F\$10="","Please enter the ownership % in F10... ",""))

The change was to delete the three characters ,"" that followed =2 in your version of the formula. If COUNTA returns 2, then return your original message. If not, then use the pair of IF at the end of the formula.

I didn't need to use the TRIM in either my original formula or your tweak to it. Sorry about that goof.

Experts Exchange Solution brought to you by