SharePoint 2010 Calculated Field Formula to Compare Texts

Question

_{Thursday, April 11, 2013 3:43 PM}

Hello,

I'm trying to compare two columns that contain text values. If Column 1 = "text1" AND Column 2 = "text2 OR text3 OR text 4", return "text 5", otherwise return "text 6". My input columns are Team and State, my output column is Region. If a person selects the team "Cash" and the state "California or Colorado or Nevada, ....etc", the region should return "Western", otherwise it returns "Eastern". And then the standards change if another team is selected. But anyway for the first team:

I tried using IF formula along with AND & OR but I keep getting errors.

Here's one formula I wrote and did not work:

=IF(AND([State]="Cash", OR([State]="California", [State]="Nevada", [State]="Colorado")),"Western", "Eastern")

Any thoughts?

Your help will be much appreciated!

Thanks,

All replies (10)

_{Thursday, April 11, 2013 6:52 PM ✅Answered | 1 vote}

You could try doing a version of the formula using only IF statements.

=IF(Team="cash",IF(State="California","Western",IF(State="Nevada","Western",IF(State="Colorado","Western","Eastern"))),"Eastern")

If the fields are Lookup fields rather than text or choice fields, the values may need to be parsed differently.

EDIT: Also, you only need the square brackets around column names in SharePoint calculated column formulas if the column names have spaces in them.

Hope that helps!

_{Thursday, April 11, 2013 4:19 PM | 1 vote}

Hi,

Your formula is incorrect.

=IF(AND([State]="Cash";OR([State]="California";[State]="Nevada";[State]="Colorado"));"Western";"Eastern")

Tip : Create the Formula's in Excel, see this link, it makes things a lot easier.
http://techtrainingnotes.blogspot.co.at/2010/08/sharepoint-creating-calculated-column.html

Regards
Pieter

_{Thursday, April 11, 2013 4:27 PM | 1 vote}

Hi ASMHF,

The formula in your question will always return Eastern, since [State] cannot be two values at the same time.  [State]="Cash" should be [Team]="Cash"

It should actually be:

=IF(AND(Team="Cash",OR(State="California",State="Nevada",State="Colorado")),"Western","Eastern")

Was that just a typo in your original post, or are you still encountering errors with the new formula?

_{Thursday, April 11, 2013 5:00 PM}

Hi,

Your formula is incorrect.

=IF(AND([State]="Cash";OR([State]="California";[State]="Nevada";[State]="Colorado"));"Western";"Eastern")

Tip : Create the Formula's in Excel, see this link, it makes things a lot easier.
http://techtrainingnotes.blogspot.co.at/2010/08/sharepoint-creating-calculated-column.html

Regards
Pieter

_{Thursday, April 11, 2013 6:05 PM}

Hi, thank you all for your replies!

Sorry it was a typo, [State] = "Cash" should be [Team] = "Cash"

The formula I used is the following:

=IF(AND([Team]="Cash", OR([State]="California", [State]="Nevada", [State]="Colorado")),"Western", "Eastern")

I still got errors though..

thriggle, the formula you suggested doesn't have squared brackets around the columns, I don't think SharePoint will recognize the fields without these [ ]. I did use the same logic though, it worked fine in Excel but I couldn't make it work in SP!! Ugh that's frustrating.. 

Does anyone know if I can actually use OR & AND to compare "text values" rather than numbers ? If so, what alternative functions should I consider?

_{Thursday, April 11, 2013 6:10 PM}

Pieter,

[State] = "Cash" should be [Team] = "Cash" , sorry for the confusion it was a typo!

I tried your formula but it still did not work :(

_{Thursday, April 11, 2013 6:43 PM | 1 vote}

Hi, 

I see you use comma's(","), you just use semicolons(";")

=IF(AND([Team]="Cash";OR([State]="California";[State]="Nevada";[State]="Colorado"));"Western";"Eastern")

Also what version of SharePoint are you using? 2007 or 2010? 

The formula above I tested in my own list with the same column names, the setup is as follows.

Team Column = Single Line of text
State Column = Single Line of text

Region = Calculated Field(Text)

Also follow the article I posted earlier...run through that step by step.Regards
Pieter

_{Friday, April 12, 2013 2:34 PM}

SharePoint 2010. Usually comma's work just fine. My team column is a Choice column, probably that's what's giving me the error then.

Thank you for your reply, I'll change it to a single text column and test your formula again :)

_{Friday, April 12, 2013 2:34 PM}

I'll give that a try!

Thank you very much,

_{Friday, April 12, 2013 3:15 PM | 2 votes}

Hi ASMHF,

Choice columns should actually be okay in the formula; they're treated the same as text fields.  It's just Lookup columns I'd be worried about!

Good luck!