After building a simple commission report using IF statements, you can further enhance the report to include multiple commission criteria.

IF functions are not limited to two criteria. You can add arguments to test for multiple criteria to help with triage.

For true or false criteria, the formula is:

if( criteria ,result if true ,result if false )

For multiple criteria, the formula follows a pattern like:

if( Criteria 1 ,result if Criteria 1 true , if( Criteria 2 ,result if Criteria 2 true ,result if both Criteria 1 and Criteria 2 are false ))

When using IF statements, if the first set of criteria is met, the calculation will return the expected results and not look for the second set of criteria to be met.

For example, if you have commission tiers that employees achieve depending on their sales, such as

• $5,000 worth of sales equals a 2% commission.
• $10,000 worth of sales equals 5% commission.
• Under $5,000 means the employees don't get commission.

To view this in a report, you'll have to build the calculation:

1. Click Calculation from the Data header to display Table Calculations.

2. Enter if()

   

3. In the brackets, search and enter Sales total.

   

4. After the Total enter a less-than symbol (<).

   

5. Enter 5000 and a comma (,).

   

6. Next, add a 0 and a comma (,).

   

7. Then, to start the second criteria, if sales are less than $10,000, enter if and search, and enter Sales total.
8. Enter a less-than symbol (<), 10000, and a comma (,) to represent the $10,000 sales total.

9. Enter Sales total, an asterisk (*), and 0.02.

   

10. Enter Sales total, an asterisk (*), 0.05, and a final closing bracket.

    

11. Optionally, rename and change the default formatting for your report to your desired currency.

    

12. Click Save Table Calculations. The new column for the commissions displays in your report.