# SharePoint list calculated column 3 validations for IF statement

I have a SP list that contains software installation data. There is a calculated column that calculates price. There is a column called Installation Date which has 2 choices either Before Sep 30, 2020 or After Sep 30, 2020 in String. There is a request type field which has 2 choices New or Renew. The Charge Date field contains Date.

I created this formula which works but is missing the logic to filter Request Type field. I wanted some assistance on updating the IF statement.

=IF([Installation Date]="Before Sep 30, 2020",[Lic Qty]*750,(([Lic Qty]*430)/TEXT([Charge Date],"mm")))
1. For Software installed before Sep 30, 2020 and Request Type is New: cost will be \$750
2. For Software installed before Sep 30, 2020 and Request Type is Renew: cost will be \$430
3. For Software installed After Sep 30, 2020 : Price will be \$430 per license per year. Pro-rated for the current year.

Thank you for your time.

## 2 Answers

Use below formula for your calculated column:

=IF([Installation Date]="Before Sep 30, 2020",IF([Request Type]="New",[Lic Qty]*750,[Lic Qty]*430),([Lic Qty]*430)/MONTH([Charge Date]))

Note:

1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).

Microsoft official documentations:

• Thank you very much, it works as needed. Oct 6, 2022 at 16:27

I think this might be what you're after. To make it a bit simpler, I've reversed your logic. Instead of starting with the "Before" scenario, I've started with the "After", which means I don't need to use multiple conditions.

=IF([Installation Date] = "After Sep 30, 2020",([Lic Qty] * 430) / Month([Charge Date]),IF([Request Type] = "New", [Lic Qty] * 750, [Lic Qty] * 430))
• Thank you very much, I see, this is a great way to do it :) Oct 6, 2022 at 16:27