What is the Purpose of the AVERAGEIF Formula in Excel?

The AVERAGEIF formula in Excel allows you to calculate the average of a range of cells that meet specific criteria. In this step-by-step guide, we will explore the purpose of the AVERAGEIF formula and learn how to use it effectively.

What is the Purpose of the AVERAGEIF Formula in Excel?

Step 1: Understanding the Syntax of the AVERAGEIF Formula
The AVERAGEIF formula follows a specific syntax:

AVERAGEIF(range, criteria, [average_range])
  • range defines the range of cells to evaluate for the criteria.

criteria specifies the condition that cells must meet to be included in the average calculation.

  • average_range (optional) determines the range of cells to average. If not provided, the formula will consider the range parameter.

Step 2: Entering the AVERAGEIF Formula

  • Open your Excel worksheet and navigate to the cell where you want the average result to appear.
  • Type the formula =AVERAGEIF( to begin the formula.

Step 3: Specifying the Range and Criteria

  • Select the range of cells you want to evaluate for the criteria.
  • Separate the range and criteria with a comma.
  • Enter the criteria within double quotes if it is text. For example, to average cells if they contain the text “Apples“, use "Apples".

Step 4: Defining the Average Range (optional)

  • If you want to average a different range of cells other than the evaluated range, include the range after the criteria, separated by a comma.
  • The average range is optional; if not specified, the formula will consider the range parameter.

Step 5: Completing the Formula

  • Close the formula with a closing parenthesis ).
  • Press Enter to calculate the average based on the provided criteria.

Step 6: Example Usage


Let’s say you have a column of sales data in column A and corresponding product names in column B. To find the average sales for the product “Apples”, you can use the AVERAGEIF formula as follows:

=AVERAGEIF(B2:B10, "Apples", A2:A10)

By following these step-by-step instructions, you can utilize the AVERAGEIF formula effectively in your Excel worksheets, enabling you to analyze and summarize data with precision.

Discover a variety of flexible Excel license options tailored to your preferences and needs in the Office Suite, like Office 2016, Office 2019, and Office 2021, by exploring our website.

Leave a Reply