Excel has three special IF functions that perform a calculation only if a specified condition is met. They are the SUMIF, AVERAGEIF, and COUNTIF functions. These functions allow you to evaluate only one condition.
- SUMIF Adds only the cells that meet specified criteria. Example: Add the order totals for only London sales.
- AVERAGEIF Averages only the cells that meet specified criteria. Example: Average the order totals for only London sales.
- COUNTIF Counts only the cells that meet specified criteria. Example: Count the number of orders for only London.
There are also the SUMIFS, AVERAGEIFS, and COUNTIFS functions in Excel. These allow you to find the sum, average, or count if a specified condition is met; however, these functions with an “S” at the end allow you to evaluate up to 127 different criteria in a single function.
The SUMIF, AVERAGEIF, and COUNTIF functions work in a similar way and have the same arguments, the only difference is whether they find the sum, average, or count of a cell range. We’ll show SUMIF as an example but keep in mind you can substitute this with one of the other functions to get a similar result.
- Click where you want to insert the formula.
- Click the Insert Functionbutton.
The Insert Function dialog box opens where you locate the function you wish to use. You can either search for it in the Search field or select a category and select the desired function from the list.
- Type SUMIF in the search field and click Go.
- Double-click the SUMIF function in the list of results.
Be careful not to select SUMIFS, as both functions will display and the search results are next to each other.
- Enter the Range of the cells you want to evaluate.
- Enter the Criteria that defines which cells will be added.
- Enter the Sum Range, or the actual cells to be added.
To the right of each of the three argument fields are previews. Check these to ensure you’ve entered the right information for each argument.
- Click OK when you're finished.
The function is calculated and sums only the cells that meet the specified criteria.