How to Use Conditional Statements in Excel

By Zicheng Ren

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.

How to Use the IF Condition

Step 1

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

Step 2

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.

Step 3

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

Step 4

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.

Step 5

Check the result in the table cells.

How to Use COUNTIF and SUMIF Functions

Step 1

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

Step 2

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.

Step 3

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.

Step 4

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

Step 5

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.

Step 6

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.

Tips & Warnings

  • Using conditional statements such as the COUNTIF and SUMIF functions can save a lot of time processing a huge amount of data that contains many empty entries. These functions can ignore those null entries.
  • You can directly type the equation in the Function Insert bar and a hint will give you an example of what to type in the equation.
  • After you type SUMIF( or COUNTIF( you can select a range of table cells you want to apply the function to instead of typing out the equation.