Formula with IF, AND, CONTAINS
I need a formula that generates a different price depending on which options have been selected in two different columns. In one column I'm using the word premium to differentiate between the options (whether or not the text contains "premium" - because there are other options in the dropdown that do not contain "premium"). Another column has a couple of options to select from (spotlight, regular), and I need to be able to generate a different number for each, depending on whether they are also premium or not. I've got two separate equations that work for these, but when I try put them together I keep getting error messages. Here are the separate equations:
=IF(AND(CONTAINS("Premium", [Which newsletter?]@row), (CONTAINS("Spotlight", Placement@row))), "1000", "900"),
=IF(AND(CONTAINS("Spotlight", Placement@row), NOT(CONTAINS("Premium", [Which newsletter?]@row))), "800", "700")
Would appreciate any help!
Best Answer
-
Formulas don't always scale very well which is why I suggested a reference table. If you would prefer to work with a formula, then you would use something like this for the example in your original post:
=IF(CONTAINS("Premium", [Which newsletter?]@row), IF(CONTAINS("Spotlight", Placement@row), "1000", "900"), IF(CONTAINS("Spotlight", Placement@row), "800", "700"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
How many different versions of this are you needing to put together, or is it just for "Spotlight"?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I have a few different versions that I need to put together, but thought if I could figure out how to do this one, I could extrapolate to others.
For example, for one of the other versions I also need to differentiate between those that do and do not contain "premium" and then I have another column with three options (top, middle, both) and I need to be able to have different prices based on those (so six total potential outcomes/prices for that equation).
-
I would suggest creating a table that has each of the selection in the first column, the non-premium in the second column, then the premium in a third column.
Then you could use a formula like this to pull in the appropriate number:
=INDEX({Grey Columns}, MATCH(Item@row, {Purple Column}, IF(CONTAINS("Premium", [Which newsletter?]@row), 2, 1)))
Basically you use the MATCH to determine which row to INDEX and then you use the IF statement to say whether it is the first or second grey column based on "Premium" or not.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Unfortunately all of the data needs to stay in the location it's at. Recreating all of the data in new columns would be more work than just working without the formulas. Thanks for taking a look though!
-
Formulas don't always scale very well which is why I suggested a reference table. If you would prefer to work with a formula, then you would use something like this for the example in your original post:
=IF(CONTAINS("Premium", [Which newsletter?]@row), IF(CONTAINS("Spotlight", Placement@row), "1000", "900"), IF(CONTAINS("Spotlight", Placement@row), "800", "700"))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thanks so much, that works well!
-
I did try it with three options and I received an "incorrect argument set" error. Should it work with the same logic? Or do you know a way to add a third option (so instead checking for whether the Placement row contains "spotlight" or not, there would be three other variables in the Placement row, each with two potential number results ("top" = 50, 40; "middle" = 30, 20; "both" = 65, 55), depending on whether or not "premium" appears in another column in that same row?
-
Three options would look like this:
=IF(CONTAINS("Top", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "50", "40"), IF(CONTAINS("Middle", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "30", "20"), IF(CONTAINS("Both", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "65", "55"))))
Combining both formulas to account for all 4 variations in the Placement formula would be:
=IF(CONTAINS("Top", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "50", "40"), IF(CONTAINS("Middle", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "30", "20"), IF(CONTAINS("Both", Placement@row), IF(CONTAINS("Premium", [Which newsletter?]@row), "65", "55"), IF(CONTAINS("Premium", [Which newsletter?]@row), IF(CONTAINS("Spotlight", Placement@row), "1000", "900"), IF(CONTAINS("Spotlight", Placement@row), "800", "700")))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
So helpful! Thanks so much!!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!