How to Use Conditional Statements in Excel

Excel allows you not only to make a table of data but also to process the data with features that include sorting, filtering and inserting functions. Using conditional statements in Excel is especially useful for obtaining results under a specified condition when processing a large quantity of data. The IF, COUNTIF and SUMIF functions are the most commonly used conditional statements in Excel.

Type the arguments in the conditional statement.

How to Use the IF Condition

Click Formulas.

Step

Open the XLSX file containing the data you want to process and click Formulas in the ribbon.

Choose a table cell.

Step

Click one table cell that you want to fill with the result of a calculation. For this, example, D4 is the profit data, which can be calculated by applying the conditional statement on the data from Sales and Target.

Choose IF.

Step

Choose IF from the Logical drop-down list in the Function Library section.

Type conditional statements in the Function Arguments.

Step

Type the value or expression that you want to evaluate in the Logical_test box. Type the value or expression to return if the Logical_test is true in the Value_if_true box. Type the value or expression to return if the Logical_test is false in the Value_if_false box. Click OK to continue. For example, type a condition statement to check whether the Sales data is greater than the Target data. If the statement is true, Excel returns the profit according to the equation in Value_if_true; if the statement is false, it returns zero for the profit according to Value_if_false.

Check the result.

Step

Check the result in the table cells.

How to Use COUNTIF and SUMIF Functions

Choose a table cell.

Step

Choose a table cell you want to fill with the result of the COUNTIF calculation.

Type the COUNTIF() function.

Step

Type the COUNTIF function in the Insert Function bar. For example, type COUNTIF(C1:C5,"<4.0") to count how many entries exist with a value less than 4.0 among the entries from C1 to C5.

Press Enter and check the result.

Step

Press Enter to see the result in the table cell. Check the result. For example, COUNTIF returned 1 because there was only one cell with a value less than 4.0 in the example data.

Choose a table cell.

Step

Choose a table cell you want to fill with the result of the SUMIF calculation.

Type the SUMIF() function.

Step

Type the SUMIF function in the Insert Function bar. For example, type SUMIF(B2:C5,">3.0") to sum the entries with values greater than 3.0 among the cells from B2 to B5.

Press Enter and check the result.

Step

Press Enter to see the result in the table cell. Check the result. For example, SUMIF returned 22.3 by adding up the values from B2 to B5 that were over 3.0.