Average Of Numbers With Multiple Criteria in Google Sheets

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria in google sheets? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time in google sheets.

So read this article till the end, and let’s dive into it;

Average cells based on multiple criteria in google sheets1

General Formula in google sheets

=AVERAGEIFS(values,range1,criteria1,range2,criteria2)

Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria in google sheets.

  • AVERAGEIFS: In google sheets, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
  • Comma symbol (,): It acts as a separator that helps to separate a list of values.
  • Parenthesis (): The main function of this symbol is to group the elements.
  • Range: It represents the input value from your worksheet.
  • Criteria: The specific criteria or values used to calculate the average.

Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria in google sheets. In the example, the formula in H2  is as follows:

=AVERAGEIFS(sales,product,E2,region,F2)

Average cells based on multiple criteria in google sheets1

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

=AVERAGEIFS(sales,product,E2,sales,F2)

Average cells based on multiple criteria in google sheets1

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)

Average cells based on multiple criteria in google sheets1

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3rd example as follows:

  • Consider the example below to calculate the average of numbers based on multiple criteria.
  • The image below will show the input values for Columns B, C, and D.
  • Next, enter the given formula in the formula bar section.
  • Finally, we’ll see the result in cell H3.

Average cells based on multiple criteria in google sheets1

Summary

This article explains how to use the formulas in google sheets to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

Related Functions

  • Google Sheets AVERAGEIFS function
    The Google Sheets AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
  • google Sheets COUNTIFS function
    The Google SheetsCOUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
  • Google Sheets SUMIFS Function
    The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

 

Related Posts

Find the Closest Data to the Data Provided in Google Sheets

In our daily work, we may encounter such an issue that to find the closest value to a certain value. In fact, google sheets internal functions can help us solve this problem. In today’s article, we will show you how ...

Basic Array Formula With Examples in Google Sheets

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

Google Sheets SUMIFS Function

This post will guide you how to use Google Sheets SUMIFS function with syntax and examples in Google Spreadsheet. Description The Google Sheets SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria ...

Calculate Average Of Last 5 Or N Values In Columns in Google Sheets
Average last N values in columns in google sheets1

Suppose you come across a task where you need to calculate the average of the last 2 or 3 numeric values in google sheets, then what would you do? If you are new to google sheets, then your first attempt ...

Find And Retrieve Missing Values in Google Sheets
FIND AND RETRIEVE missing values1

Find and retrieve is a common basic operation in Google Sheets for tables. In out daily work, we may encounter this situation that some data was lost after several operations on a table in Google Sheets. In fact, we can ...

Calculating Average Of The Numbers in Google Sheets
_3 Average numbers in google sheets1

Are you weary of investing a lot of time and effort in manually calculating the average of the numbers by including or excluding 0 and calculating the average of the top 3 scores? Then congratulations because you have just landed ...

Calculate The Average Of The Last 3, 5, Or N Numeric Values In Google Sheets
Average the last 3_ 5 or N numeric values in google sheets1

As an Google sheets user, you might have come across a task in which you need to calculate the average values of the last 2 numeric values, and you might have done this task manually but suppose if the last ...

Add Workdays in Google Sheets
Add workdays no weekends in google sheets1

To add or subtract workdays days from a date, respecting holidays but assuming a 7-day week: you can use the WORKDAY.INTL function with an argument that tells it how many days should be subtracted/added as well as which specific holidays ...

Add Row Numbers And Skip Blanks in Google Sheets
Add Row Numbers And Skip Blanks in google sheets1

Do you ever have to input a list of numbers into a spreadsheet in Google Sheets, and some of the cells are blank? It can be difficult to keep track of what number is in which cell when you have ...

Add Months To Date In Google Sheets
Add Months To Date in google sheets1

It is important to adjust time periods when performing financial modeling. In Google Sheets, you can use the MONTH function to add a specific number of months to a date. The MONTH function takes one argument: the number of months ...

Sidebar