IF AND calculated field in sharepoint

Question

I need to calculate this and return a yes or no

Column 1 =1
Column 2 if less than $1499
Return yes

Column 1 =2
Column 2 if less than $1960
Return yes

There are a total of 14 possibilities in column 1

This works if just looking a 1 option

=IF(AND([Column 1]<=1,[Column 2]<=1499),”YES”,”NO”,)

The minute you add additional options it quits working.

=IF(AND([Column 1]<=1,[Column 2]<=1499),”YES”,”NO”, IF(AND([Column 1]<=2,[Column 2]<=3999),”YES”,”NO”))

Answer 1

Hi @Dena Hobbs ,

IF function syntax: IF(logical_test,value_if_true,value_if_false). If you try to use multiple IF functions, the value_if_false should be replaced by another IF fuction. For Example: =IF(AND([Column 1]=1, [Column 2]<=1499), "Yes", IF(AND([Column 1]=2, [Column 2]<=1960), "Yes", "No")).

So you could use following formula(Remember to replace number in formula):

=IF(AND([Column 1]=1, [Column 2]<=1499), "Yes", IF(AND([Column 1]=2, [Column 2]<=1960), "Yes", IF(AND([Column 1]=3, [Column 2]<=2960), "Yes", IF(AND([Column 1]=4, [Column 2]<=3960), "Yes", IF(AND([Column 1]=5, [Column 2]<=4960), "Yes", IF(AND([Column 1]=6, [Column 2]<=5960), "Yes", IF(AND([Column 1]=7, [Column 2]<=6960), "Yes", IF(AND([Column 1]=8, [Column 2]<=7960), "Yes", IF(AND([Column 1]=9, [Column 2]<=8960), "Yes", IF(AND([Column 1]=10, [Column 2]<=9960), "Yes", IF(AND([Column 1]=11, [Column 2]<=10960), "Yes", IF(AND([Column 1]=12, [Column 2]<=11096), "Yes", IF(AND([Column 1]=13, [Column 2]<=12096), "Yes", IF(AND([Column 1]=14, [Column 2]<=13096), "Yes", "No"))))))))))))))  

My test result:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.