Open In App

Average Cells Based On Multiple Criteria in Excel

Last Updated: 14 Mar, 2024

S

Sameeksha Khandelwal
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

An Average is a number expressing the central or typical value in a set of data, in particular the mode, median, or (most commonly) the mean, which is calculated by dividing the sum of the values in the set by their number. The basic formula for the average of n numbers x1,x2,……xn is

A = (x1 + x2 ……..xn)/ n

Excel AVERAGE Function

In Excel, there is an average function for this purpose, but it can be calculated manually using SUM and COUNT functions like this

= SUM(D1:D5)/COUNT(D1:D5)

Where,

SUM: This function is used to find the sum of values in the required range of cells.

COUNT: This function is used to get the count of the required range of cells containing numeric values.

Excel provides a direct function named AVERAGE to calculate the average (or mean) of the numbers in the range specified in the function’s argument. In this function, a maximum of 255 arguments can be given (numbers/cell references/ ranges/ arrays or constants).

Syntax:

= AVERAGE(Num1, [Num2], …)

Where

Num1 [Required]: Provide range, cell references, or the first number for calculating the average.

Num2, … [Optional]: Provide additional numbers or a range of cell references for calculating the average.

Example: AVERAGE(C1:C5)

This will give the average of numeric values in the range C1 to C5.

Note: In the above-mentioned methods, any criteria for calculating the average cannot be mentioned. For this excel provide another function called AVERAGEIF.

Average Cells Based On One Criterion with Average If Function

Excel provides a direct function named AVERAGEIF to calculate the average (or mean) of the numbers in the range specified that meets particular criteria specified in the function’s argument.

Syntax:

= AVERAGEIF(Range_Cells_For_Criteria, Criteria, [Range_Cells_For_Average])

Where,

Range_Cells_For_Criteria [Required]:

Give the range of cells on which criteria are to be tested.

Criteria [Required]:

This is the condition on which to decide the cells to average. The criteria can be given in the form of an expression(logical), text-value or number, or reference to a cell, e.g. 100 or “>100” or “Apple” or A7.

Range_Cells_For_Average [Optional]:

Specify the cells on which the average needs to be calculated. Range_Cells_For_Criteria is used to find the average if it is not included.

Note:

  1. An empty cell in average_range is ignored here.
  2. Cells in the range that contain TRUE or FALSE are ignored here.
  3. If the range is blank, return the #DIV0! Error value.
  4. An empty cell in the criteria is treated as 0.

Using Double Quotes (“”) In Criteria

In Excel, the text values are surrounded by double quotations (“), but integers are not, in general. But when numbers are used in criteria(s) with a logical operator, the number and operator must also be wrapped in quotes. 

Examples:

1. To calculate the average of only positive numbers excluding 0 in the range A2 to A6
= AVERAGEIF(A2:A6,”>0″) is correct.
= AVERAGEIF(A2:A6,>0) is incorrect as the logical operator should be included in double quotes.

2. To get the average age (AGE is in column C) of the employees living in the city having id = 1 (CityId is in column D)
= AVERAGEIF(D2:D10, “=1”, C2:C10) is correct
= AVERAGEIF(D2:D10, 1, C2:C10) is also correct as here also D2 to D10 cells will be checked for value 1.

3. To get the average age (AGE is in column C) of the employees living in a city named Mumbai (City is in column B)
= AVERAGEIF(B2:B10, “Mumbai”, C2:C10) is correct 
= AVERAGEIF(B2:B10, Mumbai, C2:C10) is incorrect as text should be included in double quotes.

Example 1: To calculate the average of only positive numbers excluding 0 in the range A2 to A6.

Solution: In this example, a single criterion is given- “>0″. So all the numbers in the range that are greater than 0 come in the criteria. The average will be calculated as

(12.7 + 87.2 + 100) / 3 = 66.66666667

So, the average formula for the above example is

AVERAGEIF(A2:A6,”>0″)

criteria1

example

Example 2: To get the average age (AGE is in column C) of the employees living in a city named Mumbai (City is in column B).

Solution: Let’s break the example into different parts so that the individual parts can then be substituted in the AVERAGEIF function

  • Criteria: “Mumbai”.
  • Cells for criteria: B2 to B6.
  • Cells for average: C2 to C6 that meet the criteria.

The average age of the employees living in Mumbai

= (65 + 45) /2 = 55

So, the average formula for this example is

AVERAGEIF(B2:B6,”Mumbai”,C2:C6)

criteria2

example

AVERAGEIFS Function in Excel

The AVERAGEIFS function is used to calculates the arithmetic mean of all cells in a range that meet the specified criteria.

Average Cells Based On Multiple Criterion

Excel provides a direct function named AVERAGEIFS to calculate the average (or mean) of the numbers in the range specified that meets multiple criteria specified in the function’s argument.

Syntax:

AVERAGEIFS(RangeForAverage,RangeForCriteria1,Criteria1,
[RangeForCriteria2, Criteria2],..)

Here

  1. RangeForAverage [Required]: Provide the cell range on which the average needs to be computed.
  2. RangeForCriteria1: Range on which criteria1 is applied.
  3. Criteria 1Criteria 1: The first criteria to apply on the RangeForCriteria1.

AVERAGEIFS Function

Consider the below points to get a clear understanding of how the function works, and avoid errors:

  • In the RangeForAverage argument, empty cells, logical values TRUE/FALSE , and text values are ignored. Zero Values are included.
  • If criteria is an empty cell, it is treated as a zero value.
  • If RangeForAverage doesn’t’ contain a single numeric value, a #DIV/0! error occurs.
  • If no cells meet all of the specified criteria, a#DIV/0! error is returned.
  • AVERAGEIFS’s criteria may apply to the same range or different ranges.
  • Each criteria_range must be of the same size and shape as RangeForAverage, otherwise a #VALUE! error occurs.

Note:

  1. All ranges are required along with their criteria.
  2. Criteria 1 is required, the rest are optional.

Excel AVERAGEIFS Formula

Let’s understand the AVERAGEIFS formula stepwise:

Pick your Range:

Decide on the range of numbers you want to average. It could be a bunch of sales figure, test scores, or anything else you need an average for.

Set your Criteria:

Think about what conditions your numbers need to meet. Is it sales above a certain amount? Scores from a specific month? These are your criteria.

Follow the Formula Dance:

In the Formula, first tell Excel the range of numbers you want to average. Then, list your c criteria in pairs, where the first is the range to check, and teh second is teh criteria itself. You can have as many pairs as you need.

Odd is the Magic number:

Your AVEARGEIFS Formula should have an odd number of arguments. That means it starts with your average range and then pairs up the criteria ranges and criteria themselves.

Example 1: To calculate the average age of the employees living in Mumbai whose ages are > 50.

Solution: The following criteria are used to solve the problem

  • RangeForAverage: C2 to C8.
  • Criteria 1: “Mumbai”.
  • RangeForCriteria1: B2 to B8.
  • Criteria 2: “>50”.
  • RangeForCriteria1: C2 to C8.

Here, the average is calculated on the basis of two criteria- Employees living in Mumbai and employees whose Age > 50

AVERAGEIFS(C2:C8,B2:B8,”Mumbai”,C2:C8,”>50″)

criteria4

example

Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-.

Solution: The following criteria will be used for the AVERAGEIF function

  • RangeForAverage: C2 to C11.
  • Criteria 1: “=1”.
  • RangeForCriteria1: B2 to B11.
  • Criteria 2: “>500”.
  • RangeForCriteria2: C2 to C11.

Here, the average is calculated based on two criteria- Product having Category ID 1 and Price greater than 500

AVERAGEIFSC2:C11,B2:B11,”=1″,C2:C11,”>500″)

criteria5

example

Using Values From Another Cell

The concatenation of text can be used to use the value from another cell. Concatenation is the process of connecting two or more values to form a text string. There are two ways to concatenation of the strings in Excel

  1. Logical & operator can be used.
  2. Concatenate function in Excel for concatenation of two texts.

Example: To get average marks (marks are in column B) of passed students having minimum passing marks value contained in cell D2.

Solution: In this example, the logical & operator will be used to concatenate the two conditions for finding the result

  • Marks greater than 0.
  • Minimum passing marks i.e. 10.

Here, D2 has a value of 10, so the minimum pass marks are 10, the following criteria will be used- “>=”&D2. This will concat “>=” & value contained in cell D2, i.e. 10. After concatenation, the criteria will become “>=10”.

So, in this example

  • RangeForAverage- B2 to B12.
  • Criteria1- Marks greater than 0.
  • RangeForCriteria1- B2 to B12.

The following formula will be used to calculate the average

=AVERAGEIFS(B2:B12,B2:B12,”>=0″&D2)

criteria6

example

AVERAGEIFS with Wildcard Characters

Excel provides wildcard characters that can be used in the criteria. Some wild cards are

  1. * (asterisk): This symbol can be used to represent any number of characters.
  2. ? (question mark): This symbol can be used to represent a single character.
  3. ~ (tilde): This symbol is used before the above two wild cards and also tilde itself so that when there is a need to match a text containing * or? Or ~, Excel does not treat them as a wildcard. To treat them as normal characters in Excel criteria, the tilde is used before them.

Examples: There are the product names in column A (From A1 to A10) and their respective price in column C.

  1. AVERAGEIFS(C2:C11, A:A11, “*a*”): To find the average price of all the products who have character ‘a’ anywhere in their name.
  2. AVERAGEIFS(C2:C11, A:A11, “?a*”): To find the average price of all the products that have the second character ‘a’ in their name.
  3. AVERAGEIFS(C2:C11, A:A11, “?aree”): To find the average price of all the products that have a single character before ‘aree’ in their name.
  4. AVERAGEIFS(C2:C11, A:A11, ” ~*Saree “): To find the average price of all the products that have a name is *Saree.

Example: To find the average price of all the products that have character ‘a’ anywhere in their name.

Solution: In this example

  1. Product Saree, Nail Paint, Jeans, and Lehenga contains the character ‘a’ in them.
  2. The average prices for these products will be

= (1200+ 100+ 1000+ 5000)/4 = 7300/4 = 1825 

So, the complete formula for calculating the average is as follows

AVERAGEIFS(C2:C11,A2:A11,”*a”)

criteria7

example

AVERAGE IF between two Values

When you want to find the average of values nestled between two specific points, Excel’s has few examples:

For an Inclusive Range(including boundary Values):

AVERAGEIFS(average_range, criteria_raneg, “>=value1”, criteria_range, “<=value2”)

For an Exclusive Range(excluding boundary values):

AVERAGEIFS(average_range, criteria_range, “>value1”, criteria_range, “<value2”)

In the first formula, the “greater than or equal to “(>=) and “less than or equal to”(<=0 play matchmaker, making sure the boundary values are part of the average party.

For the second formula, the “greater than”(>) and “less than”(<) criteria give a nod to values that sit inside the boundaries but leave out those right on the edges.

These formulas are versatile (work on both scenarios)- when cells to average and the cells to check are in the same column or in two different columns.

Conclusion

Using AVERAGE, it is possible to find average without any criteria. Using AVERAGEIF, the average can be calculated with particular criteria and using AVERAGEIFS, the average can be computed with multiple criteria(s).

FAQs on Average Cells Based On Multiple Criteria

What is “Average Cells Based on Multiple Criteria” in Excel?

“Average Cells Based on Multiple Criteria” is a powerful Excel technique used to calculate the average of cells that meet specific conditions or criteria. This helps you to extract more targeted and meaningful average from your data.

How to calculate the average of cells based on multiple criteria?

You can use the AVERAGEIFS function to calculate the average of cells based on multiple cells. This Function allows you to specify multiple conditions or criteria that the data must meet to be included in the calculation.

Is there a limit to the number of criteria I can use with AVERAGEIFS?

No, there is no specific limit to the number of criteria you can with the AVERAGEIFS Function. You can include as many criteria as needed to refine your average calculation.

What are the alternatives to AVERAGEIFS for averaging cells based on multiple criteria?

You can also use the SUMPRODUCT Function to average cells based on multiple criteria that include dates, text, and numeric values. It provides the flexibility to work with various data types simultaneously.



Article Tags :

S

Sameeksha Khandelwal
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Asha Senthilkumar

How to Sum Values Based on Criteria in Another Column in Excel?

article_img
In Excel, we can approach the problem in two ways. [SUMIF Formula and Excel Pivot] Sample data: Sales Report Template We are creating a summary table for Total Product sales. Approach 1: Excel Formula SUMIF Step 1: Copy “Column A” Products and then paste into “Column F” Step 2: Remove duplicate products in “Column F” Step 3: Type “Sales” in cell G1. Step 4: Now make use of the below formula. Syntax: SUMIF(range, criteria, [sum_range]) Where, range : Required. Excel range of cells, to apply criteria.criteria : Required. criteria example : 12, ">12", t7, "5?" or "blue*".sum_range : optional. Excel range to add Now write SUMIF() in cell G2 =SUMIF($A$1:$A$21,F2,$C$1:$C$21) Step 5: Select cell G2 and drag till cell G6 Approach 2: Using Pivot table Step 1: Select the entire data range (A1:C21) Step 2: Now click Insert >> PivotTable to open the Create PivotTable dialog box Step 3: In the PivotTable Fields pane, drag the criteria column name (Product) to the Rows section, drag the column you will sum (Sales), and move to the Values section Pivot Table: Total Products Sales [Column E and F]
Read More

S

SweetSakshi

INDEX and MATCH With Multiple Criteria In Excel

article_img
Excel is a wonderful tool and helps you to perform difficult tasks easily by using the defined functions. One such function in MS Excel is INDEX and MATCH. INDEX and MATCH are two functions, but they are used combinedly to find a value using multiple criteria. In other words, you can look up and return values even if there are no unique values to look for. The INDEX/MATCH formula can help you find data points quickly without involving any manual search for them and risk making mistakes. Here, we will understand the syntax and formula in full detail so that you can easily adjust the formula for your particular needs and find values using multiple criteria. There are two ways to use INDEX and MATCH with multiple criteria to find results. INDEX and MATCH with Multiple Criteria using an Array Consider the example (as shown below). Which contains information about the Employee's Name, Designation, Team, and Date of Joining. And we need to find the Date of the Joining of Anuj Goyal who is a Marketing Associate in the company. Collect the prerequisites in the different cells (As shown below). To find a value with multiple criteria in separate columns, use the generic formula below: {
Read More

A

Anjali

How to Graph Three Sets of Data Criteria in an Excel Clustered Column Chart?

article_img
A cluster Column chart is one of the most commonly used chart types in Excel. In this, the column bars related to different series are located near one other. In this article, we learn about how to graph clustered column graphs. A clustered column chart has a different bar but they are connected. They do not show the ratio in a chart because they are not stacked. There are different bars for different categories. Clustered Column Chart with Three Sets of Data in ExcelStep 1: Write the three sets of data in an Excel sheet. i.e. from A1:D14.  elStep 2: Select the data A1:D14 and go to insert. Step 3: Under the chart section, Select the column. Step 4: From the drop-down list select cluster chart.  Step 5: Three sets of cluster chart appears. In this chart, X-axis shows the Monthly delivery of Product 1, product 2, and Product 3 in different places i.e. Udaipur (In blue), Jaipur (In red), and CHD (In green). and the y-axis shows the no of delivery. Editing Your ChartYou can edit your chart if you are not ok with the default chart type. Editing a chart can make the data analysis easy and quick. It also customizes your data. You can change the color of the columns to make them more attr
Read More

G

Gaurav Sharma

VLOOKUP On Multiple Criteria Columns Using Helper Method & CHOOSE Function

article_img
In this article, we will see how we can combine multiple values, and use them as lookup criteria for searching specific value(s) in the excel dataset. For this purpose, we will use the VLOOKUP formula, the most basic formula to perform a lookup for value(s) on excel. We will see a modified form of VLOOKUP to perform a lookup using multiple values. First, let us see a general formula: Formula: =VLOOKUP(v1&v2,dataset_part,column,0) Here, Here v1 and v2 signify those two cells, which will be used for lookup in combination.The Dataset_part signify the starting cell value and the ending cell value, upon which lookup is to be performed. This can consist of the whole dataset as well.The column signifies from which column the value has to be picked up.Here 0 signifies the approximation of lookup in vlookup. It can be set to 1 to enable approximation. Now, let us see in brief, or a summary, how VLOOKUP will work in the Multiple Criteria Column. Here, we have 2 ways of modifying VLOOKUP to make it capable of doing a lookup on multiple criteria, they are: Helper Column.CHOOSE function. Helper column will be a sort of redundant work, as we have to provide merged values of those columns on
Read More

Y

Yash Dubey

How to Find the Percentage of Two Cells in Microsoft Excel?

article_img
If you want to find the percentage of two cells in Microsoft Excel, Simply select an empty cell, where you want to display the percentage of the two cells. Type in the following formula in the selected cell : =address of cell1/address of cell2 For example, I want to print out the percentage of C2 and C3, so the formulae will be: =C2/C3, in an empty cell C4. As soon as you click on the return key, You will get your percentage calculated in cell C4. However, if a decimal value is displayed in the cell. Like 0.10, Simply press the shortcut key Ctrl+Shift+% to format as a percent or convert the numeric value into the percentage style and press the Return key. The Decimal value will be converted to a percentage. BeforeAfter You can also go to the Number section under the Home tab and press the % Symbol to convert to percentage format. % symbol under the Number listbox
Read More

S

Sameeksha Khandelwal

How to Sum Diagonal Cells in a range in Excel?

article_img
The calculation of matrices is one of the most difficult numerical computation tasks. You may need to add values diagonally in a table when performing mathematical computations. Excel allows you to sum diagonal numbers without having to add the values one by one. We'll go through how to sum cells diagonally down or diagonally up. Example: The image below shows a matrix & we want to add the elements of the diagonal. Diagonal1 is the right diagonal & Diagonal2 is the left diagonal as its top element is on the left. Conditional Formatting (On Matrix or Table) : To do conditional formatting use the following steps :Step 1: Select the Matrix/Table. Then Go to the home tab & select the conditional formatting option. Step 2: Choose New Rule. Step 3: Select: "Use a formula to determine which cells to format" & in the rule description give the format values for the formula. Here we give 1st cell name of the matrix in the formula, i.e., column(B2) - row(B2) (For diagonal) Step 4: Click on the format. Choose the color, fonts, etc, for formatting & then click on OK. Step 6: Click on Ok. You will get your cells formatted. How to Sum Diagonal Cells in a range in Excel? A ran
Read More

R

Rishabh Mishra

Excel: Modifying Columns, Rows, and Cells

article_img
Excel: Modifying Columns, Rows, and Cells - Quick StepsOpen MS ExcelSelect Row, Column or a CellPerform a Right-click >>Perform Actions Start ModifyingExcel is a powerful tool for organizing and managing data, and understanding how to efficiently modify rows, columns, and cells is crucial for anyone using it. Whether you're working with a small spreadsheet or a large dataset, being able to change column widths, row heights, insert new rows or columns, and merge or unmerge cells will help you navigate Excel more effectively. In this guide, we'll walk you through practical steps for modifying rows, columns, and cells, from adjusting the size of rows and columns to hiding or un-hiding them. We’ll also cover how to wrap text and merge cells, which are essential for better presentation and data organization in your spreadsheets.Disclaimer: Always save your work before making modifications to avoid accidental data loss. Table of ContentRow and Column in ExcelHow to Change Column Width in Excel (2 Methods) How to Change Row Height in Excel (2 Methods)Inserting, Deleting, Moving, and Hiding Rows and ColumnsWrapping Text and Merging CellsRow and Column in ExcelBefore going further you
Read More

R

Rishabh Mishra

Excel: Formatting Cells

article_img
Excel Formatting ShortcutsIf you have closely followed the previous parts of this tutorial, you already know most of the Excel formatting shortcuts. The table below provides a summary. Shortcut FormatCtrl+Shift+~ General format Ctrl+Shift+! Number format with a thousand separators and two decimal places. Ctrl+Shift+$ Currency format with two decimal places and negative numbers displayed in parentheses Ctrl+Shift+% Percentage format with no decimal places Ctrl+Shift+^ Scientific notation format with two decimal places Ctrl+Shift+# Date format (dd-mmm-yy) Ctrl+Shift+@ Time format (hh: mm AM/PM) Excel opens with all the cells looking the same. Things can get hard to understand if you have a lot of them. Changing Excel's look is as simple as changing the font and size parameters to modify the appearance of your written work. The key points become more apparent, and the material becomes more accessible. Excel also lets you choose whether the numbers are money ($) or percentages (%). Then, it will know just what to display to them. After these easy changes, your Excel file will look better and be easier for everyone to understand. Format Cells in ExcelTable of Content How to Change the F
Read More

P

Purva Tonde

How to Expand Cells to Fit the Text Automatically in Excel?

article_img
We all know how useful Excel is to store tabular data. We can do calculations in excel, we can store any information that is in the form of tables, and so on. But there are some common problems that we all face while using Excel. One of the problems that we encounter while entering oversized, overlength data in a cell. Whenever we enter any data that is not fitted in the cell every time we need to resize the column width and that is too frustrating. This article will show you how to make your cells automatically change their width whenever any oversized data is entered. Steps to make Rows/Columns Fit Text Automatically in Excel Initially, we have a table with column names “Name” and “Address". When we enter the data in the cells the data does not get fully fitted in the cells. Now, let's look at the steps to be followed: Step 1: Right-click on the sheet name in the bottom-left corner. Step 2: Now select “View Code”. Step 3: Select “Worksheet” from the drop-down menu as shown in the image. Step 4: You will see a console window with some code. Now, Enter the below code of the line in the console on the second line. Cells.EntireColumn.AutoFit Now close the window from th
Read More

E

Everything tech

Hide Error Values and Indicators in Cells in Excel

While working with formulas in spreadsheets, we might get error values. These error values are often shown in the cells as error indicators. Sometimes, they are helpful in resolving errors but other times when errors need not be corrected, these error values just make the spreadsheet look cluttered. In such a situation, it is better to hide these error indicators to not only make the spreadsheet look clean but also to prevent these errors from stopping other formulas to work correctly. Let us discuss the different ways in which we can do that but first let us look at the problem statement with an example.  Hiding Error Values and Indicators in MS Excel Open a new excel sheet and follow the steps given below. Step 1: Go to the Math and Trig section on the formulas tab and click on the quotient option from the drop-down menu. You must see a pop-up like this: Select the Quotient formula  Step 2: In this pop-up, enter 4 in the numerator and 0 in the denominator.  Enter values in the pop-up Step 3: Click ok. You will get an error like this:  The div error  You can see how this error is indicated with an exclamatory mark since division by 0 is not defined. Some other error values can be
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox