g. how to use multiple criteria in AVERAGEIF function. Data / Get & Transform / From Table/Range. In our following & new dataset, the sales of computer devices of different brands are. For example, the following formula with return the sum of all values in cells C2:C9 that are greater than or equal to 200 and less than or equal to 300. . Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. One is Name Box and another is Formula Box. Average one column based on multiple criteria in other columns; essentially a plural AVERAGEIF, allowing you to add multiple filter conditions to determine what rows to average. , row 2) with the Month of the collection date in row 1, and referring to that column in an AVERAGEIFS. 1. 1) select an empty cell 2) click on Copy 3) select the range of cells in Column J containing the data or simply select the whole column 4) select PasteSpecial > Add > OK. Average for multiple criteria in one. edited Feb 8, 2020 at 16:31. In this example, the Excel Averageifs function identifies rows where: The value in column A is equal to 1; and The entry in column B is equal to "North" and calculates the average of the corresponding values from column D. To include all October dates you should use either "<="&DATE (2023,10,31) or "<"&DATE (2023,11,1) Average with multiple criteria in same and different column. AVERAGEIF with multiple criteria. Here, the average is calculated on the basis of two criteria- Employees living in Mumbai and employees whose Age > 50. One or more cells to average, including numbers or names, arrays, or references that contain numbers. I can create the AVERAGEIF on each page to get the price average for each product types: =AVERAGEIF (range of product types;"name of the product type. With AverageIfs the conditions are used with an AND, not with an OR in the logic. The three most common measures of central tendency are: Average, which is the arithmetic mean, and is calculated by adding a group of numbers and then dividing by the count of those. 1. RangeForCriteria1: C2 to C8. COUNTIF(S), SUMIF(S), with which the use of entire column references has virtually no detriment to calculation performance, array-processing functions must calculate over all cells passed to them. I instead just used a set of nested IFS and will eventually look at changing these particular pivots to regular tables with index lookups to enable the actual data to be in multiple columns. Specifying Multiple Range. As per the Averageif syntax, which is as follows, we can use only one criterion in the criteria_range. Well, either SUMPRODUCT or array formula. It is important not to use simply AVERAGE function but divide SUM by SUM (COUNT) instead. I have used a small range for my sample below, but only make it big enough to be sure to cover your data or the calculation overhead will be significant. 5. AverageIFs multiple columns when meets multiple criteria in one column. average_range is required. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. Now, write down the VBA code to calculate the average of multiple ranges. =SUMIF({Butler - PROJECT MASTER - Inte. . This can be a single column or row, or even a combination of multiple columns and rows. AVERAGEIF (or AVERAGEIFS) is not the function you want. The AVERAGEIFS function syntax has the following arguments: Average_range Required. My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach. =AVERAGEIFS (G:G,G:G,">0",A:A,"PHA") Spot on, Thank you. As the formula is copied down, it returns total hours in Sheet1, Sheet2, and Sheet3 for the projects shown in column E. The is “Vegetables”. Re: AVERAGEIF Multiple Columns Hi, In addition to above example, it is average of "D4:H4", means in case of multiple occurrence it will not work. Applying AVERAGE and FILTER Functions Applying AVERAGE and FILTER functions is one of the smartest ways to calculate. Rather than spanning dates across the columns on a single row, you should use a single date column. I took it for granted that #N/A errors in one of the ranges would cause a problem. Hi All, I am trying to calculate average based on multiple column. In Excel 2016+ (may be different in earlier versions): select some cell within the data table. Range("M:M"), _ Sheets(modelName). Select the range of cells you want to evaluate. AVERAGEA: Returns the numerical average value in a data set. e. As Jeff pointed out, the function will silently resize your ranges to suit that restriction - so for example if you typed:. 97. I have a tab with a table. COUNTIFS: Excel 2007+: Counts the number of cells within a range that meet multiple criteria: IF: Specifies a logical test to perform: ORIf you want to include all the types you simply omit this condition, if you want to include selected types you can use array constant as shown above. AVERAGEIFS requires that all the conditions be true. Calculate Average based on multiple condition in Excel. What I'm trying to do is create a new spreadsheet that will give me an average of multiple weeks for specific individuals. Example 1: AVERAGEIFS with One Character Column. Criteria Required. 2. To use AVERAGEIF, you simply need to click on the cell where you want the function, and type the syntax of the AVERAGEIF function. If you want to do the same with AVERAGEIFS you'd need to create two additional columns with the entry/exit dates/times added, e. Here is a screenshot to clarify. Note: you might wonder why we don't use the SUMIF function with a 3D reference to sum multiple worksheets with criteria? The problem is that SUMIFS, COUNTIFS, AVERAGEIFS, etc. On my display sheet, have a list of IDs in column B, various data in the other columns, and the week number in column X. Averageifs for multiple criteria. Example of Data Table being used. In this case, the AVERAGEIFS function comes as a savior. Excel: averageif function for more than one cell. goaldFor column i want to add-up all values and devide by the number of values. I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. This sort of ambiguous situation it can't compute. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. AverageIf (Columns (1), _ DateValue ("10/1/2012"), _ Columns (2)) MsgBox av End Sub. Data / Get & Transform / From Table/Range. 1. I've tried using the standard AVERAGEIFS combined with the MONTH forumla, but I think the row of dates is throwing it off. Now, Excel SUMIFS cannot look up matching values for horizontal criteria. This cell range contains multiple columns. criteria_column1 – The. The issue is, as you can see in the picture, RANGE and AVERAGE_RANGE paramet. In this instance I want to exclude the zero value from the calculation. Please help with a formula. Step 3: Enter Formula for Calculated Average Field. 2. When the criterion is a number value, simply enter the. Method-3: Averaging Cells That Match Text Exactly. Hot Network Questions A man wants to create a bomb shelter in a small cave on his land, but there's no visible water source, except a dripping wall. 2 Apply AVERAGEIFS Function for Multiple Columns. Sample usage. Using the AVERAGEIF function is quite similar to using the SUMIF functions in the cells. Excel averageif with specific cells. The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not. However, you could re-create AVERAGEIF quite easily: So the formula in G2 to re-create AVERAGEIF could be: =AVERAGE (IF (A2:A5=G1,D2:D5-B2:B5)) Note: This formula is an array formula and needs to be confirmed through Ctrl Shift Enter. The criteria values are entered on the. SUM will also get the average of G (where E="A") for every row of F=0 if the same. Name Prior. I have tried using the OR function, and tried the curly brackets, but both give me errors. AverageIfs(TempRange. Matching and returning values to populate column. kckliodna; Aug 25, 2023; Excel Questions; Replies 3 Views 452. I should have remembered the use of the INDIRECT function to mash multiple columns together from your and other people’s past posts. I discovered the QUERY function and 'Google Visualization API Query Language'. Since you only have a single criteria in this case AverageIf should suffice. "<1000") Select the range of cells you want to average. Repeat steps 2-5 for each column or row you want to compare. Nov 7, 2021 at 0:27. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. The formulas in D1:D2 are shown in E1:E2. 0. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta (if yellow) + counta (if red)). criterion is the condition to be tested. in this example Temprange. However, the result needs to ignore any zeros that appear in the data. The formula makes use of a virtual array, containing the values of column C and the output of the mmult () function. 1. Fill one cell depending on value of another. The AVERAGEIFS uses the following syntax: average_range, criteria_range1, criteria1, criteria_range2, criteria2, etc. There are multiple of each phase and I want to calculate the average % for each phase. First part SUMPRODUCT (C3:M17* (A3:A17=750)* (C1:M1=15)) gives you sum of all values from range C3:M17 where corresponding value in column A equals to 750 and corresponding value in row №1 equals to 15. It is the first range that is evaluated. Returns the average of a data column depending on multiple criteria. Basically, the AVERAGEIF function estimates the average based on a single criterion. Calculating Average by multiple columns. Improve this answer. The article below explains two approaches. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. Probably the simplest way to handle this situation is to create a "helper" column in your workbook that contains the AND/OR functions, and then create an average of that column. To average numbers based on multiple criteria, you can use the AVERAGEIFS function. Average (1,2,3) is not same as Average (Average. I am trying to sum a total with multiple criteria (a name descriptor and a date). AVERAGEIF based on multiple columns in google spreadsheet. . Type the formula ‘=VSTACK (‘ to start the VSTACK function in Excel. Enter the following formula in S2: =--(R2>0)Average for Multiple Criteria. Range("A1")) The problem i'm running into is that the last criteria range does not run through the full column of data in. See the. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. Long story short, I'm trying to average multiple columns of number data based upon if the column matches criteria. Group Average A 113. Failed attempts. Let’s use the procedure discussed in the following section. AverageIFs multiple columns when meets multiple criteria in one column. Expected output would be "4". In the UI, open the Advanced Editor. The worksheet includes values for the last several years. 3. Using AVERAGE and IF Functions For instance, to average the sales in column C if the date in column B is between 1-Sep and 30-Oct, the formula is: =AVERAGEIFS(C3:C15, B3:B15, ">=9/1/2022", B3:B15, "<=10/30/2022") With cell references: =AVERAGEIFS(C3:C15, B3:B15, ">="&E3, B3:B15, "<="&F3) This article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. 3. But you seem to want one OR any of several conditions to be true, in conjunction with another group of conditions where two of them must always be true. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub. So I would like to select the 2011 year, the month of May and get the average of the values in column C that satisfy these requirements. Steps:The AVERAGEIF function returns the average of cells in a range that meet criteria you provide. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. The Microsoft Excel AVERAGEIFS function returns the average (arithmetic mean) of all numbers in a range of cells, based on multiple criteria. One approach would be to do a Group By on Group using Average as your aggregate. Sorted by: 1. =AVERAGEIFS(B:B,A:A,"=Apple",A:A,"=Pear",C:C,"=Yes"). Still an array formula. The AVERAGEIFS function calculates the average of all sales values in the range C3:C11 if the corresponding cells in the range A3:A11 (Sales Manager) with six characters; and the corresponding cells in the range B3:B11 (Bonus Date) should be greater than or equal to 10-May-20. Select the range you will average all vlookup findings, and click Kutools > Content > Advanced Combine Rows. One approach is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. I have about 8 other columns of Data to be included What i need is a formula that. I am trying to use the following formula: =AVERAGEIF(P30:P67;1;AQ30:AS67) So, I need to average on multiple columns, but. The second formula is based on the FILTER function and the AVERAGE function. Green 10. However, you can use the following formula as a. I am trying to calculate average based on multiple column. We have marked both criteria with a Yellow color. And each row is given a category. AverageIFs multiple columns when meets multiple criteria in one column. Cross posted multiple sites. Otherwise it increases the value of the previous cell by 1. To test if the date is within the year of 1999 you can use 2 criteria: AVERAGEIFS (N:N,A:A,">="&DATE (1999,1,1),A:A,"<"&DATE (2000,1,1)). AVERAGEIF is only available in Excel 2007+. 1 Answer. AverageIFs multiple columns when meets multiple criteria in one column. 5. 00, the price for a Medium Blue Hoodie. And you always get a #DIV/0 when you try and average nothing. Average Based on Multiple Criteria on a Single Column. Select the columns or rows you want to compare. Add a comment | 2 Answers Sorted by: Reset to. By thatguytg53 in forum Excel Formulas & Functions Replies: 11 Last Post: 03-11-2014, 06:17 PM. g. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Method-2: Finding Average If Cell Contains Text (Multiple Text Criteria) 2. to sort all the average of every column of the table and keep only the 3 maximum (using a query) Below is what I tried : the following query only gives me the average of the first column =QUERY({17:22};"SELECT AVG(Col1) WHERE Col1<>0";1)I resolved the issue by averaging Averageif function for every individual column, this worked fine. The column headers are now values in the new column, [Measurement], and the values which were in the individual columns are now all in the new column, [Value], and have the appropriate label in the [Measurement] column. Let’s assume you have the below table where we have the number of items in the stock and their selling price. The formula I made was the following:In column C, there are 3 formulas, to verify the result of the AVERAGE formula. The basic expression of this array formula displays as below: {=INDEX (array,MATCH (1, (criteria 1=lookup_array 1)* (criteria 2= lookup_array 2)…* (criteria n= lookup_array n),0))} Let’s say you want to find the sales amount of mango occurring on 9/3/2019, you can enter. 1. AVERAGEIFS for Multiple Criteria with Numbers. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. Cross posted multiple sites. What I'm trying to do is find the average for each category, excluding the 0s. We can use the following formula to do so: =AVERAGE (week1!B2, week2!B2, week3!B2) The following screenshot shows how to use this formula in practice: The “Average Points” column contains the average of the points scored for each player across week1, week2, and week3. E. Average multiple values from multiple columns in Excel. The difference between Averageif and Averageifs in Google Sheets is in the criteria used. =AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],. Also i noticed that the AverageIF doesn't skip blank cells in a multiple range it reported the #DIV/0! error, when i removed the averageif for the blank columns data was retrieved successfully but wrong values. 1. Using =averageifs with multiple text criteria in different columns. Averageif multiple columns. To get an average of data in a column with multiple criteria the following formulae may be used. What I need is a formula to provide the average temperature for that location between the two dates. Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. Criteria is the condition to apply, along with any logical operators that are needed. That didn't work either. Average for multiple criteria in. I'd just have to order those fields to get the result I want. 1. this formula finds the average of the values $223,000 and $125,000 (from cells D2 and D3). You can add as many criteria_range,criteria in AVERAGEIFS. Because we already have unique departments in column F, we can reference the list directly. I need to calculate an average when two criteria are met, but these criteria are in two separate columns. ) Otherwise, when you propogate down for subsequent row averages,. e. 00, the price for a Medium Blue Hoodie. I have no problem running this on a single column of data but get lost when trying to apply the average to multiple columns. I. Formula to create a cartesian product from two data sets in a spilled range. Average with multiple conditions. If done correctly then excel will put {} around the formula. Joined Feb 1, 2021. To write a longer formula that gives you the same result,. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). Use the following formula for G1 and copy down to the bottom of your list in column G. 1. originally I thought that it might be because of the merging, but when I use just one of the ranges it works, for example: =AVERAGEIF ( (A120:B124), "Individual Training", (K120:K124)) Register To Reply. So if I need, say, a. This is the syntax of the AVERAGEIFS function. The first argument is average_range; this is the range that contains the values to average. . The result is the average of values in group "A" for all three months of. Applying Multiple Criteria in AVERAGEIFS Function. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. The AVERAGEIFS Function [1] is an Excel Statistical function that calculates the average of all numbers in a given range of cells, based on multiple criteria. Viewed 505 times. The generic syntax for AVERAGEIF looks like this: = AVERAGEIF ( range, criteria, average_range) The AVERAGEIF function takes three arguments: range, criteria, and. Averageifs with Or. AverageIFs multiple columns when meets multiple criteria in one column. 1. 1. Method-4: Find the Average of Cells That Match Text Partially. – Spinner. Since your. The result returns 7 (average value). In this video we will be learning calculating conditional average from multi column data. ; The function treats the cell as a ‘0’ value if any cell in the criteria range is empty. I have to find average number of days where Status = new or open, Severity = 4, and Area = Apps. Use AVERAGEIFS, which lets you select criteria for averaging: =AVERAGEIFS (A3:Z3,A$2:Z$2,"=Tier 1",A3:Z3,">0") Then, when specifying the range for the criteria, be sure you lock the row number. Here, we will find out the average of goals when Goals number is at least 1, and when the Venue is Home. Thus, 5 columns are reserved for the category of a given record. The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria. The answer should be 4. Then enter the Range, Criteria, and Average_range in the Function dialog box and select Done. 55k 7 29 60 asked Dec 11, 2020 at 11:32 VHes 15 5 You are getting DIV/0 because your AVERAGEIFS function requires that ALL criteria be met on each entry. In G1 get the average value for in column C for all seconds matching what is in F1. Sample Usage. One or more cells to average, including numbers or names, arrays, or references that contain numbers. Here's a formula for that:Calculating Average by multiple columns. For example, if the values are in Column A, the Start Row is stored in C2, End Row in C3, and Percent in C4, then the formula to return your desired result would be: =AVERAGEIF(INDEX(A:A,C2):INDEX(A:A,C3), ">="&C4)The expression AVERAGEIFS represents “Average value with multiple IF”. Our next example is based on a situation when we have blanks in multiple columns. In the worksheet shown, the formula in cell H5 is: =AVERAGE(FILTER(data,group=G5)) where data (C5:E16) and group (B5:B16) are named ranges. If you need to add two criteria, for example the average quantity of shirts that Joe orders, use a combination of AVG and. Criteria Required. In this section, we will use. AVERAGEIFS evaluates all the conditions row by row and no row will meet both those conditions. If the list is empty null is returned. The Excel AVERAGEIFS function returns the average of all numbers in a specified range based on multiple criteria. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. For example, we are given the total cost of preparation of three items. Paste the M-Code below into the window that opens. Calculate average of numbers in a column using column number. AVERAGE: The AVERAGE function returns the numerical average value in a dataset, ignoring text. Trying to run an AverageIfs that includes multiple criteria and a calculation. With AVERAGEIFS, you can specify multiple criteria to calculate the average only for the values that meet all the specified conditions. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. In the next screen shot, the AVERAGEIFS formula checks for Pen in column A, and a quantity greater than or equal to 10, in column B. In. Syntax is AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3,. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . AVERAGEIF Google Sheets Syntax. I have a spreadsheet with a col of phases (Phase 1, Phase 2, etc. In this sub-method, we will calculate an average of the cell values of the Quantity column only where the cell values in the Product and Month columns are not blank. The total amount for the groceries is $78. Excel AverageIf to get total average of multiple rows from a table. That’s my simple answer to the above question. Now follow the instructions at the top of that screen. So if I need, say, a. I currently use a SUBTOTAL function to either sum, count or average a bunch of cells in a range. 0. Description. Criteria_range1 is required, subsequent criteria_ranges are optional. I'm trying to make use of Averageifs with multiple criterias in multiple columns. Formula. This help content & information General Help Center experience. AVERAGEIF vs AVERAGEIFS While Considering Criteria. Make sure to start the formula with Equals Sign. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working]. (1) Work out the sum for each column separately and total them up. I have used your code of calculating rolling average but. Subsequently, insert the following. The criteria is supplied in pairs: first the range, and then the criteria. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29. You can use the following formula to calculate the average value by date in an Excel spreadsheet: =AVERAGEIF (A1:A10, C1, B1:B10) This particular formula calculates the average value in the cell range. 2. The 2 conditions are mutually exclusive, so you're presenting nothing (null) to the average function (within the AVERAGEIFS). I have created a simple formula using AVERAGEIFS - using multiple criteria, it selects rows that match the criteria and returns an average for the numbers which occur in the selected column for those matching rows. Criteria_range1, criteria_range2,. Microsoft released AVERAGEIFS which also supports multiple conditions. We covered all possible comparison operators in detail when discussing Excel SUMIF function, the same operators can be used in SUMIFS criteria. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. 4. UPDATE2: I'm including another screenshot. Then select the merged cells. In the above, the sum of the blue/greens is 23, their count is 5 making the average 4. 2. criteria_range is the set of categories containing the specific criterion whose average will be taken. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. Another way to open the Visual Basic Editor is to simply right-click on the sheet and select View Code. Thread starter conlan; Start date Jul 23, 2014; C. Today I will show you a quick and easy way to take the average of a group of data with multiple criteria. columns(2). I basically need to get the average of the values in the J column of all rows left after those three criteria are met. The table has over 30 different item names that are taking the average of over 180 types of items as the columns and dates as the rows. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. To start off, let's calculate a straight average with the AVERAGE function for reference. I have attached a sample of the sheets I am using. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29. [like excel =AVERAGEIFS(average_range, criteria_range1, criteria1,. Using SUMPRODUCT Along Multiple Columns and Rows. This is some simple data that is similar to the data I'm actually working with. AverageIFs multiple columns when meets multiple criteria in one column. Average for multiple criteria in one column. 15. Actual Calculation of this Formula: For each time F does not equal 0 (even when E of the row does not equal "A"), return to SUM the AVERAGE of F (where E="A", even average in the zeros): 9* (18/5) [missing red highlighting in row 25]. @phxdrupal To be more specific, the reason for requiring explicitly setting the equality for column D, when you are using whole column references, is because your reference then includes the column header, which is text that does not evaluate to TRUE or FALSE. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. Repeat steps 2-5 for each column or row you want to compare. What are multiple condition averages? They are averages based on a column of numbers that include rows that meet one or more criteria. If you want to do the same with AVERAGEIFS you'd need to create two additional columns with the entry/exit dates/times added, e. As a worksheet function, the AVERAGEIFS. For example: Player A scored an average of 6. I'd like to calculate the average duration per TestName (column A) between the 2 timestamps (B and C) in another sheet. The latter creates a column with 1's if 'Franrkijk vakantie' is found in that row AND the date in column E matches the date in L1. Double click the AVERAGEIFS command. Windows; Dec 6, 2011. So for example, say I want the average between 09:00:00 and 09:30:01. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. The function was introduced in Excel 2007. I am trying to sum a total with multiple criteria (a. Both of these ranges contain only one column. AVERAGEIF based on multiple columns in google spreadsheet. Where Column B contains the numbers being averaged, and column A is your zones. ) and col of percentage values for each phase. Second part SUMPRODUCT ( (C3:M17<>"")* (A3:A17=750)* (C1:M1=15)) gives. Here is a solution using a helper column: Select all five sheets by clicking on the sheet tab of Sheet1, then Shift-clicking on the sheet tab of Sheet5. AVERAGEIF (criteria_range, criterion, [average_range]) Let’s look at the first case. Blank and text values are ignored. ) The AVERAGEIFS function syntax has the following arguments: Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))* (C2:G10<>""),C2:G10)) Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. This can be accomplished by using multiple AVERAGEIFS function to the formula. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. up to 255 total. g. The excel AVERAGEIFS function is used to find the average of values in excel where more than one (multiple) conditions satisfy. AVERAGEIFS works even if there are blank or text values in the measured column. You can use the AVERAGEIFS() formula to achieve what you want.