Combining If and AND statements

CaraBart28

I cannot seem to get this formula to work no matter how I set it. I am trying to combine three columns to get an outcome.

If CK Type is Hypertension AND Ed language is English, AND Cuff size is Large, then the outcome would be "Hypertension English Large"

There are 4 possible outcomes.

All data in the screenshot is made up.

Thank you!

Screenshot 2023-09-19 225728.png

=IF(AND([CareKit Type Requested]@row = Hypertension, [CareKit Educational Language]@row = English, [Blood Pressure Cuff Size]@row = Standard Cuff (<16.5 in.), = "Hypertension Standard English", IF(AND([CareKit Type Requested]@row = Hypertension, [CareKit Educational Language]@row = Spanish, [Blood Pressure Cuff Size]@row = Standard Cuff (<16.5 in.), = "Hypertension Standard Spanish", IF([CareKit Type Requested]@row = Hypertension, [CareKit Educational Language]@row = English, [Blood Pressure Cuff Size]@row = Large Cuff (>16.5 in.), = "Hypertension Large English" IF(AND([CareKit Type Requested]@row = Hypertension, [CareKit Educational Language]@row = Spanish, [Blood Pressure Cuff Size]@row = Large Cuff (>16.5 in.), "Hypertension Large Spanish")))))))

Paul Newcome

Give this a try:

=[CareKit Type Requested]@row + " " + LEFT([Blood Pressure Cuff Size]@row, FIND(" ", [Blood Pressure Cuff Size]@row) - 1) + " " + [CareKit Educational Language]@row

Paul Newcome

Give this a try:

=[CareKit Type Requested]@row + " " + LEFT([Blood Pressure Cuff Size]@row, FIND(" ", [Blood Pressure Cuff Size]@row) - 1) + " " + [CareKit Educational Language]@row

CaraBart28

@Paul Newcome this worked perfect! Could you just explain the formula and how it winded up working just so I can have a better understanding so that when we expand it I can update the formula?

Paul Newcome

It is basically just stringing the cell data together.

The only part that wasn't just "adding" the cells together (with spaces in between) is the middle portion where we used the LEFT function to pull the leftmost word out of the cell for the size, and we used the FIND function to tell it how many characters to pull from the LEFT going up to the first space.

CaraBart28

Hi @Paul Newcome,

This worked great until they changed the name of the kits on me. So if we have CK Type is Hypertension AND Ed language is English, AND Cuff size is Large, then the outcome would be "HTN Kit Eng Lg" how would i create the formula to where it adds those three columns to equal he specific CareKit name?

Thank you,

Cara

Paul Newcome

You would have a couple of options. One would be going back to a nested IF or series of nested IFs (series would be easier to manage in my opinion).

To do the series, you would write out three separate nested IFs (one for each section) and then add them together. Examples below:

=IF([CareKit Type Requested]@row = "Hypertension", "HTN", IF([CareKit Type Requested]@row = "Somethign Else", "SMTG"))

then

=IF([CareKit Educational Language]@row = "English", "Eng", IF([CareKit Educational Language]@row = "Spanish", "Sp"))

and

=IF([Blood Pressure Cuff Size]@row = "Large", "Lg", IF([Blood Pressure Cuff Size]@row = "Medium", "Md"))

Then we "add" them all together like so:

=CK_Nested_IF + " " + Language_Nested_IF + " " + Size_Nested_If

.

My personal preference though would be to use a second sheet with a few tables that can be pulled from to allow for things to be much more dynamic in the even of future changes.

Basically you would need 6 columns. A set of 2 for the CareKit dropdown and output, a set of 2 for the Language dropdown and output, and then a set of 2 for the Size dropdown and output.

Then you can use an INDEX/MATCH to pull the appropriate output based on the dropdown selection, replicate it for the other two pieces, then "add" them together.

=INDEX({Reference Table CK Output Column}, MATCH([CareKit Type Requested]@row, {Reference Table CK Dropdown Column}, 0)) + " " + ....................................

CaraBart28

@Paul Newcome, would it only be one for the CareKit dropdown and output since there is currently only one type of kit?

This is what I gathered from your response but I am not sure I am fully getting what you mean...

CaraBart28

@Paul Newcome

I took a stab at using the formula, however, there is no option for "THEN," so I added AND however it keeps returning as unparseable.

=IF([CareKit Type Requested]@row = "Hypertension", "HTN"), AND(IF([CareKit Educational Language]@row = "English", "Eng", IF([CareKit Educational Language]@row = "Spanish", "Sp")), AND(IF([Blood Pressure Cuff Size]@row = "Large Cuff (>16.5 in.)", "Lg", IF([Blood Pressure Cuff Size]@row = "Standard Cuff (<16.5 in.)", "Std"))))

Paul Newcome

You wouldn't use the AND statements.

Write out three separate nested IFs. One for Type, one for Language, and another for Size.

Once you get each individual nested IF working on it's own, you can "add" them together with spaces in between.

Similar to how I have it laid out in my last post.

CaraBart28

@Paul Newcome

Combined them after testing them individually and I am getting the "Incorrect argument set" error...

=IF([CareKit Type Requested]@row = "Hypertension", "HTN", IF([CareKit Educational Language]@row = "English", "Eng", IF([CareKit Educational Language]@row = "Spanish", "Sp", IF([Blood Pressure Cuff Size]@row = "Standard Cuff (<16.5 in.)", "Std"), IF([Blood Pressure Cuff Size]@row = "Large Cuff (>16.5 in.)", "Lg"))))

Paul Newcome

Because you aren't supposed to "combine" them. You "add" them together with spaces in between.

=CK_Nested_IF + " " + Language_Nested_IF + " " + Size_Nested_If

CaraBart28

@Paul Newcome I went a different route and added in helper columns, and will use the original formula you suggested. However, I cannot get this formula to work correctly for the cuff size. if it is standard I want it to return as blank. If it is large I want it to return as "Extra Large Cuff 16.5” – 18.75.” It keeps returning as unparseable.

=IF([Blood Pressure Cuff Size]@row = "Standard Cuff (<16.5 in.)", "", IF([Blood Pressure Cuff Size]@row = "Large Cuff (>16.5 in.)", "Extra Large Cuff 16.5” – 18.75””))

Paul Newcome

It looks like there are a couple of issues.

First it looks like you have some quotes out of place there towards the end.

I also see some slanted quotes. Those are called "smart quotes" which (ironically enough) are not recognized as valid characters in a Smartsheet formula. You will need to retype them in Smartsheet, here in the Community, or in a text editor such as Notepad (not Word).

CaraBart28

Hi @Paul Newcome,

Thank you for your response, I was trying to get it to show exactly how the vendor had it as using the quote marks to notate inches, but all were removed and I was able to get the formula to work correctly. Thank you for all of your help on this!

Paul Newcome

To do that, I suggest a double apostrophe.