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.
How to Use the IF Condition
Open the XLSX file containing the data you want to process and click **Formulas** in the ribbon.
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** from the **Logical** drop-down list in the Function Library section.
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 in the table cells.
How to Use COUNTIF and SUMIF Functions
Choose a table cell you want to fill with the result of the COUNTIF calculation.
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** 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 you want to fill with the result of the SUMIF calculation.
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** 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.