Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Mixing ands and Or within an If formula
I am trying to get if purchased as stock is not checked AND a sting of ORs, then check this box.
=if(and([Purchased as Stock]@row=0,if(or(islank(Client@row),isblank(Item@row),isblank(Quantity@row),isblank([Purchase Price]@row),1,""))
Answers
-
Hey @Craig Lemberger
Try this
=if(and([Purchased as Stock]@row=0,or(islank(Client@row),isblank(Item@row),isblank(Quantity@row),isblank([Purchase Price]@row)),1)
Note how the ORs are closed with a parethesis then the AND is closed.
Does this work for you?
Kelly -
So nice of you Kelly, but it still is unparseable. :(
-
hey @Craig Lemberger
I see one ISBLANK you have misspelled that I didn’t catch earlier
-
@Kelly Moore Indeed you are right. With that correction the comment went from unparseable to incorrect argument set. I love this but it drives me crazy sometimes. :)
-
Add another parenthesis in front of the ,1
Sorry but it’s hard to count parentheses when working off a phone. As mentioned earlier, you have to close both the And and the OR- in addition to any functions within those functions like ISBLANK. If your ending parenthesis isn’t blue, you don’t yet have the correct number of parentheses. The placement might not be correct, but the count is.
kelly -
thank you @Kelly Moore
I am close I think: I counted the parenthesis and even noted that they matched, but something is still no working:
=IF((AND([Purchased as Stock]@row = 0, IF(OR(ISBLANK(Client@row), ISBLANK(Item@row), ISBLANK(Quantity@row), ISBLANK([Purchase Price]@row))))), 1, "")
-
You put the second IF back in the formula as well as adding extra parenthis at the beginning. Neither belong.
=IF(AND([Purchased as Stock]@row = 0,OR(ISBLANK(Client@row), ISBLANK(Item@row), ISBLANK(Quantity@row), ISBLANK([Purchase Price]@row))), 1)
If this gives you an error, please take a screenshot of your formula with the colored text and parentheses.
Kelly
-
Are you good? Did you get it working?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 501 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 79 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I have looked through all of the answers for this topic, and still cannot find a solution that works for me. I have a sheet with a multi-select dropdown used to code responses. In another sheet, I wan…
- I have 1 sheet that has duplicated Classroom IDs but different scores on each line. I need to flag any instances during the same term of a difference over 1.00 higher or lower with the same Classroom …
- I have two formulas that are fairly identical but one is producing a #NO MATCH error. =COUNTIFS(Shop:Shop,Shop@row,[Review Start Date]:[Review Start Date], YEAR(@cell)= YEAR([Review Start Date]@row)) …