AVERAGEIF Function

Returns the average of a range of numbers that meet a given criteria
Sample usage
AVERAGEIF([First Results]1:Data25, >25)

Syntax

AVERAGEIF(range, criterion, [average_range])
  • range
    The range of cells to evaluate for the specified condition
  • criterion
    The condition that defines which numbers to add (for example: 15, “Hello World!”, >25)
  • average_range—[optional]
    The range of cells to average
Examples

This example references the following sheet information:

 CustomerItem Quantity Ordered
1JoeShirt10
2JoeHat8
3SallyShirt20

Based on the table above, here are some examples of using AVERAGEIF in a sheet:

FormulaDescriptionResult
=AVERAGEIF(Customer:Customer, "Joe", [Quantity Ordered]:[Quantity Ordered])Averages the values in the Quantity Ordered column, but only for the rows where the Customer is Joe9
=AVERAGEIF(Item:Item, "Shirt", [Quantity Ordered]:[Quantity Ordered])Averages the values in the Quantity Ordered column, but only for the rows where the Item is Shirt15

If you need to add two criteria, for example, the average quantity of shirts that Joe orders, use a combination of AVG and COLLECT

Still need help?

If you have an Enterprise plan, you can use AI to help with formula creation.

Use the Formula Handbook template to find more support, resources, view 100+ formulas, a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community

Was this article helpful?
YesNo