Formula will function in Excel but not SmartSheet

rebecca.sand82251

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?

Find more posts tagged with

Manufacturing

Formulas

IT & Operations

Conditional Formatting

Comments

Nic Larsen

Try this:

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

Nic Larsen

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

Bruce Case

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

Paul Newcome

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

rebecca.sand82251

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

rebecca.sand82251

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

Paul Newcome

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.

rebecca.sand82251

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

Paul Newcome

Excellent!

Sign in to join the conversation!

Quick Links

Trending discussions

SUMIF function not working
=SUMIFS({GM Compliance Trained}, {GM Compliance Training Dept}, Department@row , {Compliance Training Date}, MONTH(7, 8, 9)) GM Compliance Trained is the number of employees trained. I am trying to sum the number of employees trained in each department during 3rd quarter (July 1-Sep 30). Thinking I need an IFERROR in there…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…