## How to average cells based on multiple criteria in Excel?

In Excel, most of us may be familiar with COUNTIF and SUMIF functions, they can help us to count or sum values based on criteria. But have you ever tried to calculate the average of values based on one or more criteria in Excel?

**Average cells based on one criterion with Averageif function**

**Average cells based on multiple criteria with Averageifs function**

** Average cells based on one criterion with Averageif function**

Supposing, I have the following data range, column A is the list of product and column B is the orders, now, I will average the order cells where the product is KTE.

Please enter this formula into your desired cell: **=AVERAGEIF(A2:A19,D2,B2:B19)**, (* A2:A19* is the data which contains the criteria,

*refers to the range you want to average and*

**B2:B19***is the criterion you want to average the values based on), and press*

**D2****Enter**key to get the result, see screenshot:

** Average cells based on multiple criteria with Averageifs function**

If you want to calculate average with multiple criteria, the **Averageifs** function may help you.

The Averageifs syntax as follows:

**AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)**

**Average_range**: is the range of cells to average;**Criteria_range1**,**criteria_range2**, … are the ranges in which to evaluate the associated criteria;**Criteria1**,**criteria2**, … are criteria which you are based on.

Take the above data for example, now, I want to average the order of KTE and the order greater than 500. Please do as this:

Enter this formula into a blank cell: **=AVERAGEIFS(B2:B19,A2:A19,E1,B2:B19,">500")**( * A2:A19* is the data which contains the criteria1,

*refers to the range you want to average,*

**B2:B19***and*

**E1***are the criteria1 and criteria 2), then press*

**>500****Enter**key to get the result you want. See screenshot:

**Note:** If you have more than two criteria that you want, you just need to add the criteria ranges and criteria you need as this: **=AVERAGEIFS(C2:C19, A2:A19, F1, B2:B19, F2, C2:C19, "<500")**, (* A2:A19* and

*are the first criteria range and criteria,*

**F1***and*

**B2:B19***are the second criteria range and criteria,*

**F2***and*

**C2:C19***are the third criteria range and criteria,*

**< 500***refers to the range you want to average the values), see screenshot:*

**C2:C19****Related articles:**

How to average top or bottom 3 values in Excel?

How to average last 5 values of a column as new numbers entering?

How to average every 5 rows or columns in Excel?

### Best Office Productivity Tools

Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely

**Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...**

**Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel** (Full-Featured 30-Day Free Trial)

#### Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)