AVERAGEIFS for Multiple Criteria in Different Columns in Excel

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


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.


4 Ideal Examples of AVERAGEIFS for Multiple Criteria in Different Columns in Excel

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.

averageifs multiple criteria different columns

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.

Find Average Sales from Multiple Criteria in Different Columns with AVERAGEIFS

Read More: Difference Between AVERAGEIF and AVERAGEIFS in Excel


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.

Use AVERAGEIFS for Multiple Criteria in Different Columns Involving Date

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.

Get Average Age from Multiple Criteria in Different Columns Using AVERAGEIFS

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.

Insert AVERAGEIFS Function to Compute Average Marks in Excel

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.

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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo