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.
Video of the Day
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.