Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# How to Use the AVERAGEIF Function in Excel

We can use the AVERAGEIF function to calculate the average of numbers in a range that meet the specified criteria. Logical operators like >, <, <>, = and wildcards (*,?) for partial matching are supported by the criteria of AVERAGEIF. The steps below will walk through the process.

Figure 1- Final result of the AVERAGEIF function

## Syntax

`=AVERAGEIF(range,criteria,[average_range])`

• Range: This could be one or more cells, a named range, arrays, or references.
• Criteria: This could be a number, an expression, cell reference, or text.

• Average_range [optional]: These are the cells to get the average. If we omit it, the range is used.

## Formula

`=AVERAGEIF(\$C\$4:\$C\$13,">5000")`

## Setting up the Data

• The Branches of a store will be entered into Column B
• Column C will contain the Sales for each of the stores
• The result will be returned in Column E with the specified criteria in Column D

Figure 2 – Setting up the Data

## AVERAGEIF IF Sales is >5000

• We will click on Cell E5
• We will insert the formula below into Cell E5
`=AVERAGEIF(\$C\$4:\$C\$13,">5000")`
• We will press the enter key

Figure 3- AVERAGEIF if Sales is >5000

In a similar fashion, we will enter the formula below into Cell E4 and Cell E6 to get the result

• Cell E4: `=AVERAGEIF(\$C\$4:\$C\$13,">0")`
• Cell E6: `=AVERAGEIF(\$C\$4:\$C\$13,"<5000")`

Figure 4- Result of applying the AVERAGEIF function

## Note

• Any cell in a range that contains arguments like TRUE or FALSE are ignored
• Any empty cell within the range is ignored in range and average_range with AVERAGEIF Function
• The error value #DIV/0! will be returned if there aren’t cells in the range that meet the criteria

## Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Are you still looking for help with the Average function? View our comprehensive round-up of Average function tutorials here.