# Formula will function in Excel but not SmartSheet

edited 12/09/19

For some reason I am having trouble getting this formula to function in SmartSheet (even though it works in Excel)...

I am wanting to populate a "Y" in 'Valid Approval?' column only when one of the following 2 situations occur:

1) 'Primary Approval' says "Approved" AND 'Secondary Approval' says "Approved"

2) 'Primary Approval' says "Approved" AND 'Secondary Approval' is blank

Valid approval?    Primary Approval    Secondary Approval

Y                            Approved

N                           Approved                   Submitted

N                           Submitted                 Approved

Y                           Approved                  Approved

This is the formula I wrote that functions in Excel...

=IF((COUNTIF(C2,"Approved")+COUNTIF(D2,"Approved"))*OR(COUNTIF(D2,"Approved")+COUNTIF(D2,"")),"Yes","No")

Any tips on how I get this to function in SmartSheet?

• ✭✭✭✭✭✭

Try this:

=IF(OR([Primary Approval]@row = "Approved", [Secondary Approval]@row = "Approved"), "Y")

• ✭✭✭✭✭✭

I revised mine a little after thinking about it more:

=IF(AND([Primary Approval]@row = "Approved", OR([Secondary Approval]@row = "Approved", ISBLANK([Secondary Approval]@row))), "Y", "N")

• ✭✭✭✭✭

Another option.  If both are Approved, it will populated Y, otherwise, populate N

=IF([Primary Approval]1 = "Approved", IF([Secondary Approval]1 = "Approved", "Y", "N"), "N")

• ✭✭✭✭✭✭

I personally put my vote to this one. I think this is probably the most efficient way to achieve the desired results.

• Thank you so much! This is exactly what I needed & what worked!

• Thank you so much for this! For some reason it did not work but thank you for your time!!

• ✭✭✭✭✭✭

I'm kind of curious why this didn't work. Did you get an error, or did it just not function as expected? Based on your original post this should have done the trick.

• I take that back...it is working perfectly & accounts for all variables I need to look at - this is going to work! Thanks again!

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!