Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel Functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we need to find the average value from a data range based on multiple conditions. There are multiple ways available to carry out the operation. This article will show you 4 ideal examples of AVERAGEIFS for Multiple Criteria in Different Columns in Excel.
What Is AVERAGEIFS Function?
The AVERAGEIFS function is a mathematical and statistical function in Excel that can calculate the average value from a range based on multiple criteria. The average is also called the arithmetic mean.
- Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- Arguments
average_range: The range of data from where we’ll determine the average based on certain criteria. This is a mandatory argument.
criteria_range1: This mandatory argument is about the first criteria range.
criteria1: Our first condition. We’ll look for this condition in criteria_range1.
[criteria_range2]: This is the second criteria range. But it’s not mandatory. We’ll apply this argument when we need to find the average based on conditions more than one.
[criteria2]: Our second condition is when we’ll have multiple conditions.
Similarly, we can have as many criteria ranges and criteria as we need.
AVERAGEIFS for Multiple Criteria in Different Columns in Excel: 4 Ideal Examples
The AVERAGE and AVERAGEIF functions can also calculate the average value. But they need other functions joined with them to satisfy multiple conditions in different columns. That makes the formula a complicated one. When we use AVERAGEIFS, we don’t have to rely on other functions anymore. So, that’s the biggest advantage of this function. Therefore, go through the examples to clearly understand the AVERAGEIFS and apply it to multiple criteria in different columns.
1. Find Average Sales from Multiple Criteria in Different Columns with AVERAGEIFS
In our first example, we’ll show how you can find the average sales amount of a certain product from a particular region using AVERAGEIFS. To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset contains Region, Product, and Sales. Here, we’ll find the average sales of AC from the West region. So, learn the steps below to carry out the operation.
STEPS:
- First, select cell D16.
- Then, type the formula:
=AVERAGEIFS(D5:D14,B5:B14,"West",C5:C14,"AC")- D5:D14 is the sales range, B5:B14 is the region range from where we’ll look up for West and C5:C14 is the second criteria range for AC.
- Subsequently, press Enter.
- Thus, you’ll get the precise result.
Read More: How to Use Excel AVERAGEIFS Function with Multiple Criteria
2. Use AVERAGEIFS for Multiple Criteria in Different Columns Involving Date
Moreover, you can deal with the date through the AVERAGEIFS function. In the following dataset, we have the Product, Sales, and Date of those sales. And we want to figure out the average sales amount of Cable in the month of January. Therefore, follow the step below to perform the task.
STEPS:
- Firstly, click cell F4.
- There, input 1/1/2022 to denote the first day of January.
- Now, select the cell and press Ctrl and 1 keys together.
- As a result, the Format Cells dialog box will pop out.
- Next, under the Number tab, choose Custom > mmmm to display the month name automatically.
- Afterward, press OK.
- Subsequently, select G4.
- Type the formula:
=AVERAGEIFS(D5:D14,B5:B14,">="&F4,B5:B14,"<="&EOMONTH(F4,0),C5:C14,"Cable")- The EOMONTH function gives out on the last day of January.
- Here, we have applied three criteria ranges along with the criteria.
- D5:D14 is the average range. B5:B14 is the second and third criteria to find for the dates in January Then, we’ll find the Cable in C5:C14.
- Click Enter.
- In this way, you’ll get the desired average.
Read More: How to Apply AVERAGEIFS Function Between Two Dates in Excel
3. Get Average Age from Multiple Criteria in Different Columns Using AVERAGEIFS
The following dataset contains the Weight in kg, Height in cm, and Age of some teenagers. Here, we want to determine the average age of teenagers who are above 55 kg and below 175 cm. Hence, learn the process to carry out the operation.
STEPS:
- First of all, choose cell D16.
- Insert the formula:
=AVERAGEIFS(D5:D14,B5:B14,">55",C5:C14,"<175")- Hit Enter.
- Consequently, the average age will appear.
Read More: How to Use AVERAGEIFS Function for Multiple Columns
4. Insert AVERAGEIFS Function to Compute Average Marks in Excel
Lastly, we’ll seek the average marks of students in section B in Math. By this time, you’ve become quite familiar with the AVERAGEIFS function. So, this won’t be a tough task for you. See the process below.
STEPS:
- Select cell D16 at first.
- Then, type the formula:
=AVERAGEIFS(D5:D14,B5:B14,"Math",C5:C14,"B")- After that, press Enter.
- Hence, the average marks of the desired students will emerge.
Read More: Excel AVERAGEIFS with Multiple Criteria in Same Range
Things to Remember
- This function has a limitation. We can easily do logical AND operation but we can’t carry out the OR That is, all the conditions need to be satisfied with this function.
- If the average_range is blank or text values that excel can’t translate into numbers, and if all the conditions are not met, you’ll get the #DIV0! error.
- The criteria ranges and the average range must be of the same size & shape.
- We can use wildcard characters in the function argument.
- If any cell is empty in the criteria range, this function considers it as 0.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
Henceforth, you will be able to use AVERAGEIFS for Multiple Criteria in Different Columns in Excel using the above-described examples. Keep using them and let us know if you have more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
