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
Bill GoldenExecutive Managing MemberAsked:
Who is Participating?
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.

ThomasMcA2Commented:
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")

Open in new window

0
byundtCommented:
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.
0
Bill GoldenExecutive 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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Bill GoldenExecutive Managing MemberAuthor Commented:
0
byundtCommented:
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.
0
Bill GoldenExecutive 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
0
byundtCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill GoldenExecutive Managing MemberAuthor Commented:
Thanks so much.  I am now building on your examples.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.