IF AND statement support

mmac

Hello,

I'm looking to for an if and statement that is similar to the below and actually works. :)

=IF(AND([Country]@row = "US", [Availability]@row = "SOO" ()), "[US] Product Line: All Products","")

If country = US and availability = SOO (exact match), then insert "[US] Product Line: All Products"

Thank you in advance for your support.

Werner Gerstacker

Hi @mmac

As I mentioned above, there's no need for [] around one-word column names.

If you have multi-word column names, though, you need to enclose them like this [Availability Type]

Werner Gerstacker

"When US is selected and nothing is selected under Availability Type, nothing should appear under Categories."

The you have to set up your set of nested IF functions to reflect that, e.g. like this:

=IF(AND(Country@row = "US", [Availability Type]@row = ""), "",IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "TEST", "[US] Product Line: All Products")))

Just looked at the video.

I guess with 4 countries and being able to select multiple Availability Types at once, a se of nested IFs will probably not work. You might have to think of another approach to address this. Maybe with a set of / combination of VLOOKUP or a COLLECT functions.

Werner Gerstacker

Hi @mmac

It should work if you remove the () after "SOO":

=IF(AND(Country@row = "US", Availability@row = "SOO"), "[US] Product Line: All Products", "")

Note:

Smartsheet will also remove the [] around single word column names.

mmac

Hi @WernerDoubell ,

Thank you for the reply. Using the below statement did not seem to work.

=IF(AND(Country@row = "US", Availability@row = "SOO"), "US Product Line: All Products", "")

Werner Gerstacker

Hmmm - works fine for me:

What is the actual error message?

Do you have a screenshot?

Not sure what else to check other than spelling.

mmac

@Werner Gerstacker The mistake is all mine. I failed to include the full name of the availability column which is actually below.

=IF(AND(Country@row = "US", Availability Type@row = "SOO"), "US Product Line: All Products", "")

The above does not work for me and sends off an #UNPARSEABLE error. However, if I just use 'Availability' it works. Not sure what the issue is.

Werner Gerstacker

Hi @mmac

As I mentioned above, there's no need for [] around one-word column names.

If you have multi-word column names, though, you need to enclose them like this [Availability Type]

mmac

Ah, I understand. Thank you for further explanation. It worked!

mmac

@Werner Gerstacker

I'm trying to expand on nested if statements to accommodate the following and am running into difficulty. The below is what I'm hoping to accomplish if you are able to please provide support. 

Columns: country, availability type, categories (results column)

If availaibility type = soo, then categories = N/A

If availability type = soo c pc/r, then categories = All Products

If availaiblity type = soo c, then categories = All Products

If availability type = soo pc/r, then categories = All Products

If availability type = c pc/r, or c, or pc/r, then categories = All Products

This is as far as I got and am looking for support.

=IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "C PC/R"), "TEST"))

Werner Gerstacker

For this statement you still need to enter the 'value_if_false'

=IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "C PC/R"), "TEST", "value_if_False"))

If you have a few different availability types and countries this will quickly become a nested nightmare, though.

mmac

@Werner Gerstacker I see, thank you for sharing that information with me. I think things are almost there with what I'm looking to accomplish.

Using the below statement, I'm not sure what edit to make to show US CATEGORY SELECTION, only when US is the selection made and SOO, only is the selection made under availability. Hopefully I made sense with what I'm trying to explain.

=IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "C PC/R"), "TEST", "US CATEGORY SELECTION"))

Werner Gerstacker

Not sure I understand.

=IF(logical_expression, value_if_true, [value_if_false])

As you can see in the definition of the IF function you display "N/A" when Country = US and Availability Type = SOO

If this is not the case the formula evaluates the next, nested IF function.

Currently the fallback, i.e. if now of the other evaluations reads true, is "US CATEGORY SELECTION" - this will show up for any entry in Country other than "US" and, even if the country is "US", for any Availability Type other than "SOO" and "C PC/R".

mmac

@Werner Gerstacker I apologize, please allow me to clarify.

When US is selected and nothing is selected under Availability Type, nothing should appear under Categories.

Everything with the below statement is working for the exception of including the above.

=IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "TEST", "[US] Product Line: All Products"))

This video may be helpful as example: https://www.loom.com/share/a636a5d67e0e473993ce606780663c45

Thank you again for your support!

Werner Gerstacker

"When US is selected and nothing is selected under Availability Type, nothing should appear under Categories."

The you have to set up your set of nested IF functions to reflect that, e.g. like this:

=IF(AND(Country@row = "US", [Availability Type]@row = ""), "",IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "N/A", IF(AND(Country@row = "US", [Availability Type]@row = "SOO"), "TEST", "[US] Product Line: All Products")))

Just looked at the video.

I guess with 4 countries and being able to select multiple Availability Types at once, a se of nested IFs will probably not work. You might have to think of another approach to address this. Maybe with a set of / combination of VLOOKUP or a COLLECT functions.