---
title: "AVERAGEIF and AVERAGEIFS practice: 3 exercises with answers"
description: "Practice AVERAGEIF and AVERAGEIFS in a live spreadsheet, with one condition, two conditions and averages that skip zeros."
canonical_url: "https://excelsavvy.com/practice/averageif"
meta:
  "og:description": "Practice AVERAGEIF and AVERAGEIFS in a live spreadsheet, with one condition, two conditions and averages that skip zeros."
  "og:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~QVZFUkFHRUlGIGFuZCBBVkVSQUdFSUZTIHByYWN0aWNlOiAzIGV4ZXJjaXNlcyB3aXRoIGFuc3dlcnM%2Cdescription_~UHJhY3RpY2UgQVZFUkFHRUlGIGFuZCBBVkVSQUdFSUZTIGluIGEgbGl2ZSBzcHJlYWRzaGVldCwgd2l0aCBvbmUgY29uZGl0aW9uLCB0d28gY29uZGl0aW9ucyBhbmQgYXZlcmFnZXMgdGhhdCBza2lwIHplcm9zLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIkNoYW5uZWwiLCJPcmRlciB2YWx1ZSIsIkF2ZXJhZ2Ugb25saW5lIl19LHsibiI6MiwiY2VsbHMiOlsiT25saW5lIiwiNDIwIiwiIl19LHsibiI6MywiY2VsbHMiOlsiU3RvcmUiLCIzMTAiLCIiXX0seyJuIjo0LCJjZWxscyI6WyJPbmxpbmUiLCI1ODAiLCIiXX0seyJuIjo1LCJjZWxscyI6WyJTdG9yZSIsIjI3MCIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIk9ubGluZSIsIjM1MCIsIiJdfV0sInRhcmdldCI6WzEsMl19fQ%2Cp_Ii9wcmFjdGljZS9hdmVyYWdlaWYi%2Cs_ZvxJP5ZqEcj8psPH.png"
  "og:image:height": "600"
  "og:image:type": image/png
  "og:image:width": "1200"
  "og:title": "AVERAGEIF and AVERAGEIFS practice: 3 exercises with answers"
  "twitter:image": "https://excelsavvy.com/_og/d/c_Page%2Ctitle_~QVZFUkFHRUlGIGFuZCBBVkVSQUdFSUZTIHByYWN0aWNlOiAzIGV4ZXJjaXNlcyB3aXRoIGFuc3dlcnM%2Cdescription_~UHJhY3RpY2UgQVZFUkFHRUlGIGFuZCBBVkVSQUdFSUZTIGluIGEgbGl2ZSBzcHJlYWRzaGVldCwgd2l0aCBvbmUgY29uZGl0aW9uLCB0d28gY29uZGl0aW9ucyBhbmQgYXZlcmFnZXMgdGhhdCBza2lwIHplcm9zLg%2Ceyebrow_Excel%2Bpractice%2Bexercises%2Csite_practiceexcel%2Cprops_eyJncmlkIjp7ImNvbHMiOlsiQSIsIkIiLCJEIl0sInJvd3MiOlt7Im4iOjEsImNlbGxzIjpbIkNoYW5uZWwiLCJPcmRlciB2YWx1ZSIsIkF2ZXJhZ2Ugb25saW5lIl19LHsibiI6MiwiY2VsbHMiOlsiT25saW5lIiwiNDIwIiwiIl19LHsibiI6MywiY2VsbHMiOlsiU3RvcmUiLCIzMTAiLCIiXX0seyJuIjo0LCJjZWxscyI6WyJPbmxpbmUiLCI1ODAiLCIiXX0seyJuIjo1LCJjZWxscyI6WyJTdG9yZSIsIjI3MCIsIiJdfSx7Im4iOjYsImNlbGxzIjpbIk9ubGluZSIsIjM1MCIsIiJdfV0sInRhcmdldCI6WzEsMl19fQ%2Cp_Ii9wcmFjdGljZS9hdmVyYWdlaWYi%2Cs_ZvxJP5ZqEcj8psPH.png"
---

> Index of every page on this site: [llms.txt](https://excelsavvy.com/llms.txt)

# **AVERAGEIF and AVERAGEIFS practice exercises**

Take the average of only the rows that meet one condition, or several with AVERAGEIFS, and leave zeros out of an average.

[**Start the first exercise**](https://excelsavvy.com/practice/averageif/averageif)

AVERAGEIF(AVERAGEIFS(

## **How AVERAGEIF and AVERAGEIFS work**

AVERAGEIF finds the average of the numbers in the rows that meet one condition. It takes three arguments: the range to test, the condition, and the range to average. AVERAGEIFS does the same with two or more conditions.

`**=AVERAGEIF(A2:A7,"Online",B2:B7)**` returns the average order value for the rows where column A says Online. With two conditions, `**=AVERAGEIFS(C2:C9,A2:A9,"North",B2:B9,"Shoes")**` averages the sales of shoes in the North region. Leave out the third argument and AVERAGEIF averages the tested range itself, so `**=AVERAGEIF(B2:B8,">0")**` averages only the values above zero.

Watch the argument order: AVERAGEIF puts the range to average last, but AVERAGEIFS puts it first. If no row meets the condition, you get #DIV/0!, because there is nothing to average. The exercises below cover one condition, two conditions with AVERAGEIFS, and leaving zeros out of an average.

1. [**1****Average one group with AVERAGEIF**In D2, use AVERAGEIF to find the average order value of the Online orders.](https://excelsavvy.com/practice/averageif/averageif)
2. [**2****Average with two conditions with AVERAGEIFS****Premium**In F1, find the average sale of Shoes in the North region. AVERAGEIFS starts with the range to average, then each range with its condition.](https://excelsavvy.com/practice/averageif/averageifs-two-conditions)
3. [**3****Leave zeros out of an average****Premium**The store is closed on Mondays and Sundays and records 0 sales. In E1, find the average sales of the days it was open: AVERAGEIF with the condition ">0".](https://excelsavvy.com/practice/averageif/average-without-zeros)

## **Functions in this topic**

- [**AVERAGEIF**](https://excelsavvy.com/functions/averageif)
- [**AVERAGEIFS**](https://excelsavvy.com/functions/averageifs)

## **Read the guide**

- [**Pivot tables explained, and the formulas that do the same job**](https://excelsavvy.com/learn/pivot-tables)
- [**Excel practice data: free datasets with tasks and answers**](https://excelsavvy.com/learn/excel-practice-data)
- [**Excel customer data: free practice file with tasks and answers**](https://excelsavvy.com/learn/excel-customer-data)

## **Questions**

<details>

<summary>

### Why does AVERAGEIF return #DIV/0!?

</summary>

No cell met your condition, so there was nothing to average. Check the criterion for typos and extra spaces. To show something cleaner than the error, wrap the formula in IFERROR, as in =IFERROR(AVERAGEIF(A2:A7,"Online",B2:B7),"No orders").

</details>

<details>

<summary>

### How do I average the values above a number in a cell?

</summary>

Join the operator to the cell with &amp;, as in =AVERAGEIF(B2:B20,"&gt;"&amp;E1). The comparison stays in quotes and the cell reference stays outside them. Change E1 and the average follows. Use "&gt;="&amp;E1 if the limit itself should count.

</details>

<details>

<summary>

### What is the difference between AVERAGEIF and AVERAGEIFS?

</summary>

AVERAGEIF checks one condition and takes the range to average last, and that last argument is optional. AVERAGEIFS checks one or more conditions and takes the range to average first, which is required. Every condition in AVERAGEIFS must be true for a row to be included.

</details>

```json
{"@context":"https://schema.org","@graph":[{"@id":"https://excelsavvy.com/#identity","@type":"Organization","name":"ExcelSavvy","url":"https://excelsavvy.com/","logo":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/#website","@type":"WebSite","inLanguage":"en-US","name":"ExcelSavvy","url":"https://excelsavvy.com/","alternateName":["Excel Savvy","ExcelSavvy.com","PracticeExcel","Practice Excel Online"],"publisher":{"@id":"https://excelsavvy.com/#identity"},"workTranslation":[{"@id":"https://excelopgaver.dk/#website"},{"@id":"https://excelpraxis.de/#website"}]},{"@id":"https://excelsavvy.com/practice/averageif#webpage","description":"Practice AVERAGEIF and AVERAGEIFS in a live spreadsheet, with one condition, two conditions and averages that skip zeros.","name":"AVERAGEIF and AVERAGEIFS practice: 3 exercises with answers","url":"https://excelsavvy.com/practice/averageif","@type":["WebPage","CollectionPage","FAQPage"],"breadcrumb":{"@id":"https://excelsavvy.com/practice/averageif#breadcrumb"},"isPartOf":{"@id":"https://excelsavvy.com/#website"},"mainEntity":[{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/1"},{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/2"},{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/3"}],"potentialAction":[{"@type":"ReadAction","target":["https://excelsavvy.com/practice/averageif"]}],"primaryImageOfPage":{"@id":"https://excelsavvy.com/#logo"}},{"@id":"https://excelsavvy.com/practice/averageif#breadcrumb","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","name":"Home","item":"https://excelsavvy.com","position":1},{"@type":"ListItem","name":"Excel practice exercises","item":"https://excelsavvy.com/practice","position":2},{"@type":"ListItem","name":"AVERAGEIF and AVERAGEIFS practice: 3 exercises with answers","position":3}]},{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/1","@type":"Question","inLanguage":"en-US","name":"Why does AVERAGEIF return #DIV/0!?","acceptedAnswer":{"@type":"Answer","text":"No cell met your condition, so there was nothing to average. Check the criterion for typos and extra spaces. To show something cleaner than the error, wrap the formula in IFERROR, as in =IFERROR(AVERAGEIF(A2:A7,\"Online\",B2:B7),\"No orders\")."}},{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/2","@type":"Question","inLanguage":"en-US","name":"How do I average the values above a number in a cell?","acceptedAnswer":{"@type":"Answer","text":"Join the operator to the cell with &, as in =AVERAGEIF(B2:B20,\">\"&E1). The comparison stays in quotes and the cell reference stays outside them. Change E1 and the average follows. Use \">=\"&E1 if the limit itself should count."}},{"@id":"https://excelsavvy.com/practice/averageif#/schema/question/3","@type":"Question","inLanguage":"en-US","name":"What is the difference between AVERAGEIF and AVERAGEIFS?","acceptedAnswer":{"@type":"Answer","text":"AVERAGEIF checks one condition and takes the range to average last, and that last argument is optional. AVERAGEIFS checks one or more conditions and takes the range to average first, which is required. Every condition in AVERAGEIFS must be true for a row to be included."}},{"@id":"https://excelsavvy.com/#logo","@type":"ImageObject","caption":"ExcelSavvy","contentUrl":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3","inLanguage":"en-US","url":"https://excelsavvy.com/brand/practiceexcel/icon-512.png?v=3"}]}
```

## Sitemap

See the full [sitemap](https://excelsavvy.com/sitemap.md) for all pages.
