Welcome to Function Friday. This week we’ll explore Microsoft’s SharePoint List Function “AND” function. This function will return “Yes” if all arguments are “true”. If any of them are not, it will return “No”.

Limitations

  1. Max 30 conditional values. If you need more than 30, you should break it into smaller chunks.

Arrays

  1. AND operations can be performed in bulk if you have an array with elements. For example, =AND({TRUE, FALSE}) will return No
  2. You can’t perform operations within the array-like =AND({TRUE,1=1})or nest them =AND({TRUE,AND({TRUE,FALSE})})

References

  1. SharePoint evaluates references to tables like =AND([isActive])
  2. You can combine references like =AND([isActive], True). []identify the reference to the table, but they are not necessary if the name doesn’t contain spaces. So you can write the same formula like =AND(isActive, True)

Text

  1. AND is case insensitive, so AND("TrUe") or AND("True") is evaluated to Yes
  2. AND is language-specific, so AND("Falso")(False in Portuguese) will return the #Value!error.

Operations

  1. Operations like =AND(1=2) are possible. Compatible with <, >, <=, >= or <>.
  2. Arithmetic operations are also possible. For example, OR(1+1=2)will return Yes
  3. You can nest operations like =AND(1<2,AND(1<2,TRUE))
  4. You can combine with other operators like OR=AND(1<2,OR(1<2,TRUE))

Recommendations:

  1. Avoid complex formulas. If you get a “#Value!” error, it will be hard to debug what is the condition that is generating the error.
  2. If possible, test the conditions one by one to see if you’re getting the desired results. Only after you’re sure that all of them are correct,  join them into a big list of conditions. By doing this, you are sure that each of them is returning the desired value.
  3. I recommend no more than ten conditional values. Although SharePoint supports up to 30, it will become an unmanageable mess to debug complex formulas.

Sources:

Microsoft’s Reference

Back to the SharePoint’s list formula reference.

Featured Image by Tim Mossholderon Unsplash

Manuel Gomes

I'm a Project Manager with experience in large projects and companies. I've worked in the past for companies like Bayer, Sybase (now SAP) and I'm currently working for Pestana Hotel Group.

View all posts by Manuel Gomes →

Leave a Reply

%d bloggers like this: