Seeking helps to combine two formulas into one

[Ben9108]

I would like to seek someone's assistance to simplify and combine the two formulas below into one. Thanks

Formula 1: =IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),"Lowest Price of Vendors",IF(AND($E$2=TRUE,$E$6=TRUE),"Westco",IF(OR(AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors","Incorrect or Other Vendors"))))))


Formula 2: =IF(C9=G8,"Correct","Check Vendors")

[AliGW]

Not enough information!

Does the check in formula 2 need to take priority, or should it take the place of the final value if false in formula 1?

Have you tried either of these, and if so, what is not right about their results?

=IF(C9=G8,"Correct",IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),"Lowest Price of Vendors",IF(AND($E$2=TRUE,$E$6=TRUE),"Westco",IF(OR(AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors","Incorrect or Other Vendors")))))))

=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),"Lowest Price of Vendors",IF(AND($E$2=TRUE,$E$6=TRUE),"Westco",IF(OR(AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C9=G8,"Correct","Check Vendors")))))))

[Ben9108]

Thanks for your reply

The results were not my expectation after trying the both revised formulas.

I would like to use one cell “Check 1” to check and indicate the results to let the form as simply as it could.

For example:

Photo1 ~ My expected result is “correct vendor” shown in a cell “Check 1” after checking cells C9 & G8.
Photo2 ~ My expected result is “incorrect vendor” shown in a cell “Check 1” after checking cells C9 & G8.
Photo3 ~ My expected result is “lowest price of vendor” shown in a cell “Check 1” after checking cells C9 & G8.

[JeteMc]

Try the following:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ben9108]

Thanks for your email

The revised formula was able to perform the checking function.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[JeteMc]

You're Welcome and thank you for the feedback. As AliGW states, please take a moment to mark the thread as 'Solved'. I hope that you have a blessed day.

[Ben9108]

When I run the formula in my daily operation. I encountered a problem, so I modified the formula shown below. However, it got an error. Would you mind letting me know what is the problem with the revised formula? Thanks

'=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C9=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(AND($E$2=TRUE,$E$6=TRUE),"Westco","Incorrect or Other Vendors")),"Correct Vendor","Incorrect Vendor"))))

[AliGW]

If you add another clause, you need another closing bracket:

=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C9=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(AND($E$2=TRUE,$E$6=TRUE),"Westco","Incorrect or Other Vendors"))),"Correct Vendor","Incorrect Vendor"))))

If this doesn't fix it, then once again, you will need to provide a sample workbook.

[Ben9108]

Thanks for your help, the 2nd revised formula can work normally.
For my situation, I needed to make a further modification of the formula to suit my current operation. However, the updated formula cannot be worked. Would it be possible to let me know what is the problem of the 3rd revised formula?

'=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C10=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(OR(AND($E$6=TRUE,$E$4=TRUE,$D$9="JBY"),AND($E$2=TRUE,$E$6=TRUE)),"Westco","Incorrect or Other Vendors"))),"Correct Vendor","Incorrect Vendor"))))

[AliGW]

It's the same problem again - did you read what I told you last time?

If you add another clause, you need another closing bracket:

=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C10=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(OR(AND($E$6=TRUE,$E$4=TRUE,$D$9="JBY"),AND($E$2=TRUE,$E$6=TRUE)),"Westco","Incorrect or Other Vendors")))),"Correct Vendor","Incorrect Vendor"))))

[Ben9108]

Thanks for your reply.
I tried to put the closing bracket in the 3rd revised formula, but there was an error triggered shown in the attached photo. Would it be possible to help me solve the error?

[Sam Capricci]

maybe this without the extra parenthesis...
=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C10=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(OR(AND($E$6=TRUE,$E$4=TRUE,$D$9="JBY"),AND($E$2=TRUE,$E$6=TRUE)),"Westco","Incorrect or Other Vendors"))),"Correct Vendor","Incorrect Vendor"))))

[Ben9108]

Thanks for your reply
I found the following problems after my modification of the formula. Would you mind helping me to solve the problems?

1. The result cannot show “Correct Vendor” when inputting the criteria including A+A = true, Unit Rate= true, vendors=Westco, Location=JBY
2. The result cannot show "Incorrect or Other Vendors" when inputting any criteria. Can I delete it?

=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF($C$9=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(OR(AND($E$6=TRUE,$E$4=TRUE,$D$9="JBY"),AND($E$2=TRUE,$E$6=TRUE)),"Westco","Incorrect or Other Vendors"))),"Correct Vendor","Incorrect Vendor"))))

[Sam Capricci]

Ok, looking at your posted workbook in post #14, AND your #s1 and 2 in that post, #1 does show correctly based on your workbook and the formula AliGW gave you as I adjusted it, so it returns incorrect vendor.
I am not sure about point #2 above but offhand if it isn't an answer then I would say you could delete it.
Now, I'm not sure what you are trying to do with this section in your formula, could you explain it???

=IF(COUNTIF($E$2:$E$4,TRUE)>1,"Incorrect Work Type",IF(COUNTIF($E$5:$E$6,TRUE)>1,"Incorrect Unit Rate & lowest Price",IF(OR(AND($E$4=TRUE,$E$5=TRUE,$D$9="TWP"),AND($E$4=TRUE,$E$5=TRUE),AND($E$2=TRUE,$E$5=TRUE)),"Lowest Price of Vendors",IF(C10=IF(AND($E$6=TRUE,$E$4=TRUE,$D$9="TWP"),"Standard",IF(AND($E$6=TRUE,$E$4=TRUE),"Goldstraw",IF(OR(AND($E$6=TRUE,$E$4=TRUE,$D$9="JBY"),AND($E$2=TRUE,$E$6=TRUE)),"Westco","Incorrect or Other Vendors"))),"Correct Vendor","Incorrect Vendor"))))

Is it easier to say IF(C10="Standard" or is it supposed to say that IF(C10 = standard OR goldstraw? then go on from there?

Finally, it might be easier to know what the correct answer should be based on the selections in your workbook.

[Ben9108]

Thanks for your reply and comments
I would like to use the following table to show my design criteria for your ease understanding. Would you mind helping me to solve the problem of the formula?

[JeteMc]

I feel that I may be missing something; however, based on the table shown in post #16 it seems that the formula could be:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ben9108]

Thanks for your reply
I am sorry for making you confused or misunderstanding. XYZ represents other contractors. To avoid misunderstanding, it would be better to delete the items from 9 to 12.

My problem is the formula cannot obtain the result for item 2 after the modification. Would you mind helping to find out the root causes?

[AliGW]

Are you prepared to be guided here? Your formula is far too long and unwieldy and it's almost impossible to correct it because of this.

It would be better to start from scratch instead of insisting that an ineffective and poorly written formula be 'corrected'.

[JeteMc]

Assuming that "the formula" referred to in post #18, is the formula from post #17 then make the following change:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ben9108]

Thanks for JeteMc reply and help.

I made a modification to the formula based on your idea to achieve the result. Thanks
In addition, I had a minor problem with the formula which is the cell showing “Incorrect Vendors” without any criteria input. Do you have any idea about this problem?

=IF($E$5,"Lowest Price of Vendors",IF(AND($E$6,$C$9="Standard",$D$9="TWP"),"CORRECT 1",IF(AND($E$6,$D$9<>"TWP",OR($C$9="westco",$C$9="goldstraw")),"correct 2","Incorrect Vendors")))

[JeteMc]

Assuming that cell C9 should be filled in order that the formula produce an output, try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ben9108]

Thanks for everyone's reply and help, especially JeteMc.
I am happy to learn from JeteMc and share valuable knowledge with me to solve the problem.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.