Formula with IF, AND, CONTAINS

Alyce R.

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!

Paul Newcome

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

Paul Newcome

How many different versions of this are you needing to put together, or is it just for "Spotlight"?

Alyce R.

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

Paul Newcome

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.

Alyce R.

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!

Paul Newcome

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

Alyce R.

Thanks so much, that works well!

Alyce R.

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?

Paul Newcome

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

Alyce R.

So helpful! Thanks so much!!

Paul Newcome

Happy to help. 👍️