AVERAGEIFS in Google Sheets: Criteria and Examples

In Google Sheets, use AVERAGEIFS to average values that meet multiple conditions. With sales in C2:C100, regions in A2:A100, and product types in B2:B100, enter =AVERAGEIFS(C2:C100,A2:A100,"North",B2:B100,"Retail") to average North-region retail sales. Each criterion must match its corresponding row, and all criteria must be true for a value to be included.

What AVERAGEIFS does

AVERAGEIFS calculates a conditional average: it looks at one range for the numbers to average and checks other ranges against conditions you specify. It is useful when an overall average would hide meaningful differences, such as comparing sales by region and channel, test scores by class and status, or hours by employee and project.

The function follows this general pattern: an average range, followed by one or more pairs of criteria range and criterion. For example, a condition can require a row to belong to a particular region, while another requires its sales value to be above a threshold. Google Sheets includes a row’s average-range value only when it satisfies every supplied condition.

AVERAGEIFS is not the same as averaging all the values that match any one of several conditions. Its criteria work like AND: region must be North and product type must be Retail. If you need an OR condition, such as North or West, you need a different approach, described below.

Set up the ranges and try a simple example

Imagine a sheet with Region in column A, Channel in column B, and Sales in column C. Rows 2–6 contain: North, Retail, 120; North, Online, 90; South, Retail, 150; North, Retail, 180; and West, Retail, 110. The first row is data in row 2, so each range in the formula should start there rather than including the header.

With those labels in A2:A6 and B2:B6 and the sales amounts in C2:C6, use =AVERAGEIFS(C2:C6,A2:A6,"North",B2:B6,"Retail"). The matching sales are 120 and 180; their average is 150. The North Online value and South and West Retail values are excluded because each fails at least one of the two conditions.

When building a larger formula, check these parts in order:

Average range: the numeric values you want averaged, such as C2:C100.
First criteria range: the cells to test, such as A2:A100.
First criterion: the required value or comparison, such as North.
Additional pairs: another criteria range and criterion for each extra rule.

Keep the ranges aligned. If the average range covers rows 2 through 100, each criteria range should cover those same rows. A range that starts or ends on a different row can cause an error or make the conditions line up with the wrong records.

Write criteria for text and numbers

A text criterion is usually entered in double quotes, as in "North". You can also refer to a cell containing the condition, which makes it easier to change the selection without editing the formula. With the region in A2:A100, sales in C2:C100, and the desired region written in E1, use =AVERAGEIFS(C2:C100,A2:A100,E1). The formula then averages sales for whichever region is entered in E1.

For numeric comparisons, put the comparison operator and number together inside quotes. With status labels in A2:A100 and scores in C2:C100, this formula averages scores for completed records that are at least 80: =AVERAGEIFS(C2:C100,A2:A100,"Complete",C2:C100,">=80"). The score range can be both the average range and a criteria range; the condition filters out lower scores before the remaining numbers are averaged.

To build a comparison using a value in a cell, join the operator to that cell with the ampersand. With sales in C2:C100, regions in A2:A100, and a minimum sales threshold in E1, use =AVERAGEIFS(C2:C100,A2:A100,"North",C2:C100,">="&E1). This is handy for an adjustable cutoff. The operator needs to be in quotes, while E1 is a cell reference, not quoted text.

Google Sheets criteria can also use wildcards for text matching. An asterisk represents any number of characters, and a question mark represents one character. With product names in B2:B100 and values to average in C2:C100, =AVERAGEIFS(C2:C100,B2:B100,"Pro*") includes names beginning with Pro, such as Pro plan or Product A only if its text actually begins with Pro. Use a tilde before a wildcard when you want to match the wildcard character itself rather than use it as a pattern.

Use dates and more than one condition

Dates are stored as values in Sheets, so it is generally safer to set date boundaries with DATE than to rely on how a typed date is interpreted. To average amounts in B2:B100 for dates in A2:A100 during January 2026, use =AVERAGEIFS(B2:B100,A2:A100,">="&DATE(2026,1,1),A2:A100,"<"&DATE(2026,2,1)). The start boundary includes January 1; the exclusive end boundary excludes February 1 and anything later. This pattern also works when the dates include times.

Each additional criteria range and criterion adds another required test. For example, you could require a region, a sales channel, and a date window in one formula. Make sure the labels are consistent: extra spaces in a category name or a misspelled condition can leave no rows eligible. If your spreadsheet uses a locale that expects semicolons between function arguments, Sheets may display or require semicolons instead of commas; the examples here use commas.

Understand blanks, text, and zero values

AVERAGEIFS averages qualifying numeric values. Text entries and empty cells in the average range do not contribute to the arithmetic average, while a numeric zero does. That distinction matters in a dataset where zero means an actual result, rather than missing information. For example, a recorded sale of 0 is a real value and can lower the average; an empty sales cell is not the same thing.

You can apply a condition to a range to include or exclude blank entries. With values in C2:C100 and corresponding names in A2:A100, the criterion "<>" tests for nonblank cells in the criteria range; an empty criterion can be used to find blank cells. Be deliberate about formulas that return an empty string, because those cells may look blank even though a formula is present. If missing data has a specific meaning in your sheet, decide how to handle it before interpreting the average.

Troubleshoot common AVERAGEIFS problems

The result is #DIV/0!: Often no rows meet all criteria, or the matching rows contain no numeric values in the average range. Check spelling, spaces, date boundaries, and whether the qualifying cells are numbers rather than text.
The result is unexpectedly low or high: Check for numeric zeros, hidden qualifying rows, and criteria that are broader than intended. Also confirm that each criteria range starts and ends on the same rows as the average range.
A number stored as text is ignored: A value that looks numeric may have been imported as text. Convert the source data to numbers, or inspect its formatting and contents before trusting the result.
A date condition returns nothing: Confirm that the source cells contain actual dates, not text that merely looks like dates. Check that the start and end conditions include the intended period.
The formula reports a range error: Make sure each criteria range has the same dimensions as the average range. Avoid including a header in one range but not the others.

For debugging, test one condition at a time, then add the others back. That helps reveal which criterion eliminates the expected rows. It can also help to filter the source table manually and compare the visible qualifying values with the function’s result.

Choose between AVERAGEIFS and alternatives

Use AVERAGEIF when there is just one condition; use AVERAGEIFS when there are multiple required conditions. Both functions calculate conditional averages, but the plural form makes the extra criteria pairs explicit. For a simple condition such as averaging every sale from North, AVERAGEIF is shorter. When region and channel both matter, AVERAGEIFS is clearer.

FILTER combined with AVERAGE can be useful when you want to inspect or reuse the set of matching rows. With regions in A2:A100, channels in B2:B100, and sales in C2:C100, this formula returns their conditional average, or a message if there are no matching numeric values: =IFERROR(AVERAGE(FILTER(C2:C100,A2:A100="North",B2:B100="Retail")),"No matching numeric values"). FILTER is flexible, but for a straightforward conditional average, AVERAGEIFS is usually easier to read. IFERROR also catches errors beyond an empty result, so use it only if replacing those errors with a message is appropriate.

For an OR condition, do not simply average two separate group averages unless both groups have the same number of qualifying values. That would give each group equal weight regardless of its size. A filtered list can express OR by adding Boolean conditions; alternatively, use a helper column to label rows that belong to either group, then average by that label. The right method depends on whether you need one result, a reusable filtered set, or a transparent audit trail.

Frequently Asked Questions

Can AVERAGEIFS use several conditions?

Yes. Add a criteria-range and criterion pair for every additional rule. A row contributes only when it meets all the criteria, so multiple conditions behave like AND rather than OR.

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF is designed for one condition. AVERAGEIFS supports multiple conditions, using one average range followed by pairs of ranges and criteria. For a single condition, either may be suitable, but AVERAGEIF is the more direct choice.

Why does AVERAGEIFS return #DIV/0!?

Usually no records passed every criterion, or there were no numeric values to average among the matching records. Check the condition text, range alignment, data types, and date boundaries before treating the error as a calculation problem.

Does AVERAGEIFS count zero values?

Yes. A qualifying numeric zero is included in the average and can affect the result. Blank cells and text in the average range do not contribute as numeric values, so zero should not be used as a substitute for missing data unless that is what it means in your dataset.

Can I use AVERAGEIFS with dates?

Yes. Dates can be criteria, including ranges such as on or after one date and before another. Using DATE to define the boundaries is a reliable way to avoid ambiguity about date formats and to include a complete month or other period.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.