Excel Tutorial: Understanding If and Countif Functions in Excel

If Function in Excel

The If function performs a logical test on a cell or range of cells to determine whether a specified condition has been met. For example, suppose you have decided that a Bonus will be granted to the individual who sells more than 700 units of certain product. The example below shows how you can use the If function to calculate whether the value in cell B9 meets this criteria.

1. Click in cell B16.
2. Type =IF(B9>700,"Yes","No") and press Enter on the keyboard.

Perform logical tests on data using IF and COUNTIF functions.
Here, data is analyzed to see if sales quotas have been met.

Excel evaluates the Total amount in cell B9 to determine whether more than 700 units have been sold (in other words, whether the value in B9 is greater than 700). If the value in B9 is greater than 700, (meaning that more than 700 units have been sold), the formula returns the label Yes, and you can give the individual his or her bonus. However, if the value in B9 is 700 or less, the function returns the label No, and you can decide not to grant the individual his or her bonus.

Countif Function in Excel

COUNTIF is similar to IF in that the function evaluates a cell or range of cells to test for a specific condition. However, the COUNTIF function returns the number of times the condition has been meet in the range, not whether the condition has been met at all. For example, suppose you want to figure out how many brokers have met a sales quota of 200 units. The following set of steps illustrates how to apply the COUNTIF formula to make this determination.

1. Click in cell B17.
2. Type =COUNTIF(B5:B8,">200") and press Enter on the keyboard.

Excel evaluates the values in range B5:B8 to determine how many of the cells within this range have values that are greater than 200 (in other words, how many brokers met the sales quota of 200 units.)