Hi, I am very close with my formula here, the only problem is I want the Criteria to be in W39 contains Completed rather than ='s it. I have 2 records I want it to find - "completed" and "Contractor advised completed" but if I put that into the formula like I have done below, it only works for jobs that are completed and not for Contractor advised completed.
=IF(AND(CM39=1,W39="Completed",W39="Contractor Advised Completed",AA39<>"Payment On Account"),1,0)
Any ideas?
Thanks
I'm not sure I fully understand what you are trying to do, but I beleive your formula would always return "0", since you have 2 mutually excluding logical tests in an =AND() statement. W39 cannot be equal to both "Completed" and "Contractor Advised Completed". Maybe you want to use =OR() instead?
Sincerely
S?ren Larsen
"Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"
Don't understand the Criteria to be in W39 contains Completed rather than ='s it.
Just guessing, but is something other than "Completed" being entered?
Edit:
Are you using drop down menus for data entry? If not that could solve it for the future. Can you post a workbook?
Last edited by Russell Dawson; 03-28-2012 at 06:14 AM. Reason: More thoughts
If I helped, please don't forget to add to my reputation. (click on the star below the post)
If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.
Failure is not falling down but refusing to get up.
I want it to find cells that contain the word completed, not equals the word completed.
=IF(AND(CM39=1,W39="Completed",W39="Contractor Advised Completed",AA39<>"Payment On Account"),1,0) -= This doesn't work as Soren suggested above. So I have tried:
=IF(AND(CM39=1,W39="Completed",AA39<>"Payment On Account"),1,0) - But I need the W39="Completed" to give me a cell that contains the word completed
Try using =FIND() and search for "completed". If "completed" is found in your cells it will return its position otherwise it will return #VALUE.
Try this:
=IFERROR(IF(AND(CM39=1,FIND("completed",W39)>0,AA39<>"Payment On Account"),1,0),0)
If it's just the 2 possibilities for W39 containing "completed" then a simple RIGHT() will do:
=IF(AND(CM39=1,Right(W39,9)="Completed",AA39<>"Payment On Account"),1,0)
Neither of these formulas work unfortunately. No idea why. I can't believe excel doesn't have a simple symbol for contains rather than equals or more than etc....?
I have tried googling it with no luck?
Does the formula work, but return the wrong values, or do you get an error?
If you get an error then you might have to replace commas with semicolons; i don't know the English Excel punctuation.
Last edited by Søren Larsen; 03-28-2012 at 08:57 AM.
Perhaps it's time to provide a small sample file.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks