1. Insert an empty column in column S. The result is the average of values in group "A" for all three months of. This. I have attached a sample of the sheets I am using. 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. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. To calculate an average when corresponding cells are not blank, you can use the AVERAGEIFS function. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. 5. I want the average of 'column b' data that corresponds to 'red' and 'yellow'. 7. =AVERAGEIF(A1:A50,">0") This function only includes in the average those cells that contain values greater than zero. Now, you can define your average as follows:Re: Alternative to AVERAGEIFS to calculate 2 columns with about 1 Million rows taking days I would use Pivot Table, with a few click But LF need to add formula =K2/L2/24 or Power Pivot by Insert Pivot and check add to data model Power Pivot ribbon New Measure Average =AVERAGE(Range[Value]) Max =MAX([Value]) LF. Share. I want to compute the average of every product. 1. Range("M:M"), _ Sheets(modelName). 0. Sample Usage. =AVERAGEIFS(B:B,A:A,"=Apple",A:A,"=Pear",C:C,"=Yes"). I am trying to create a formula using AVERAGEIFS that is pulling criteria from three different columns and then giving me an average score from a fourth column if all of the other three columns meet. Green 10. . Blank and text values are ignored. I tried this but failed: =AVERAGEIF(range1; criteria 1; range 2; criteria 2; average range). The AVERAGEIFS function syntax has the following arguments: Average_range Required. This is an example of what my data looks like: Database: Column A: Country Column B: Project Column C: Data 1 Column E: Data 2 Column G: Data 3. That will get you the following table. reaction [Total Won] >0. Issue - How can I find the daily average of Group A for the given dates, given that Group A are in two columns (they can't be combined as there are multiple sub groups) Now, you can see that in the AVERAGEIFS function, cell range D5:F13 is selected as average_range. Group Average A 113. Specifying Multiple Range. Averaging a range of values is easy. So if I need, say, a. ) and col of percentage values for each phase. averageifs w/ multiple average ranges in different columns. Another way to open the Visual Basic Editor is to simply right-click on the sheet and select View Code. 0. Below is one more solution for the same I want to average data in those columns if specific multiple criteria is met in other columns. The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. To write a longer formula that gives you the same result,. Returns the average (arithmetic mean) of all cells that meet multiple criteria. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. Averageif multiple columns. If column A APPROVED or TEMPAPPROVED are the only options containing APPROVED then you could use a wildcard (as in AA2) or you can change to using SUMPRODUCT (as in AA3). No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. [SOLVED] AVERAGEIF Multiple Columns Multiple occurrences. Name Prior. 3. The formula makes use of a virtual array, containing the values of column C and the output of the mmult () function. 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. Double click the AVERAGEIFS command. Basically, the AVERAGEIF function estimates the average based on a single criterion. The criteria values are entered on the. Returns the average of a data column depending on multiple criteria. The AVERAGE, AVERAGEIF, and AVERAGEIFS functions all automatically ignore blank cells and any cells that contain text. Excel: averageif function for more than one cell. Both of these ranges contain only one column. In this video show you how to calculate the average values across multiple columns with a filterin. It is important not to use simply AVERAGE function but divide SUM by SUM (COUNT) instead. Thus, 5 columns are reserved for the category of a given record. AVERAGE: The AVERAGE function returns the numerical average value in a dataset, ignoring text. 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. Firstly, select your entire dataset and press the keyboard shortcut. For example, we can compute the average of the amount column, but only include those rows where the customer column is equal. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . Suppose we would like to calculate the average value in the points column for players who are on the Mavs team. The first formula simply confirms the second formula. Each of my cells is 10 cells apart. The result returns 7 (average value). In such cases, you can use the AVERAGEIF and AVERAGEIFS functions. Clear search AverageIFs multiple columns when meets multiple criteria in one column. Here, D5:D14 is the average_range argument which is the Physics column. I want to calculate the average of the last column (SLA in days) for the lines. 2. Specify the range to check against the 1st condition (B3:B15 - items). It is one or more cells to average. If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function. up to 255 total. Thread starter Coachcasa; Start date Feb 1, 2021; C. 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. Specify the range to check against the 1st condition (B3:B15 - items). Average multiple values from multiple columns in Excel. I'm trying to make use of Averageifs with multiple criterias in multiple columns. This screenshot shows my attempt to replicate your setup: The formula in column D is based on the following steps: Find the column number corresponding to the category. Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. 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. Therefore the cells in the range are used to calculate the average. Well, either SUMPRODUCT or array formula. For example, it calculates: Row 1: 100 – 90 = 10. It also supports the specification of multiple columns to compute. This same equation has worked while using it on the same sheet, however, this equation is referencing a different sheet. Calculate Average based on multiple condition in Excel. 1. There are multiple of each phase and I want to calculate the average % for each phase. . Thread starter mgirvin; Start date Dec 6, 2011; M. Here's an. Your sheet structure could be improved to make analysis much more efficient. The above formula. The generic syntax for AVERAGEIF looks like this: = AVERAGEIF ( range, criteria, average_range) The AVERAGEIF function takes three arguments: range, criteria, and average_range . 0. Excel begin AverageIF function. To average numbers based on multiple criteria, you can use the AVERAGEIFS function. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working]. Unsure where my errors are on this one. This code is good for one row of the table but I want to get the average of all the rows and Columns in Cal table. criteria_column1 – The. Criteria Required. I'd like. e. So for example the home. 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. For instance, to get an average of sales that were. That’s my simple answer to the above question. Using =AVERAGEIF (B5:B7, “=”, C5:C7) formula, Excel will calculate an average of cell B5:B7 only if a cell in Column A in the same row is empty, as shown below: Suppose we wish to average values that correspond to blank cells and include empty strings that are returned. criterion is the condition to be tested. Ask questions for further explanation. SYNTAX. I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. Neither is working. . AVERAGEIF based on multiple columns in google spreadsheet. The formula can use any worksheet function and use any fields from the data source. You have given us a calculated range selection as C2:K22 however, the range on your attached file is C2:N22. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. Expected output would be "4". Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) 0. This screenshot shows my attempt to replicate your setup: The formula in column D is based on the following steps: Find the column number corresponding to the category. =AVERAGEIF (B5:E9,"<>0") Next, press the Enter key and this will find the Sales Average inside cell D10. . ) Otherwise, when you propogate down for subsequent row averages,. average_range is required. ; The function treats the cell as a ‘0’ value if any cell in the criteria range is empty. You clearly already know how to do it to even be asking the question you are! Assuming values are in A2:L2 (month headers at the top): =AVERAGE(A2:L2) But wait, you only wanted to average up to and not including the. 2. 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. Averageif across sheets and locations in Excel, Average if many sheets in many locations, Use an if statement to determine if cells have a non- zero value. Finally, we'll take a look at Excel's AVERAGEIFS function. So, one thing I'm thinking is that your lookup has no records or you have a mispelling. Using the parameters we’ve entered, the AVERAGEIFS formula checks for instances of anything not named “Nuts” within the range A2:A10, then checks for occurrences where values in F2:F10 are greater than 0. OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i. A couple of ways to overcome this. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column - The data column to average. 0. Syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],. For example, you need to calculate the average of three date’s quantity of Apple excluding zero cells in the table, you can apply the following formula. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. The formula: =1/ (critRng1=TRANSPOSE (crit1))*1/ (critRng2>=DATE1)*1/ (critRng2<=DATE2)*avgRng. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. 0. However, you can use the following formula as a. Paste the M-Code below into the window that opens. 1. To find the average of all cells in a range L1:L10 that meet multiple conditions: =AVERAGEIFS(L1:L10,M1:M10,"oranges",N1:N10,">=5",O1:O10,"<10"). The formulas in D1:D2 are shown in E1:E2. I've tried several 'averageif' formulas with 'and' and 'or'. The Convert Text to Columns Wizard –. Stack Overflow. Excel - Apply IF on all elements of the column before doing an Average. e. More specifically, I want to average temperature values from column H, only if the wind direction is northwest,which is detoned as "NW", the values of which are in column J, as well as if the wind speed is lower than 2 (column D). Average for multiple criteria in one. Rather than using AverageIf (), try using Average (If ()) as an array formula for this kind of scenario. Can I not put multiple criteria on the same column range in an AVERAGEIFS? Wat needs to happen is that in the Columns C the program needs to calculate the average of all the values of F IF the number in E falls within the interval in Columns A and B. It is used to scan through a range of cells checking for a specific criterion, and then giving the average (aka the mathematical mean) if the values in a range that correspond to those values. As the formula is copied down, it returns total hours in Sheet1, Sheet2, and Sheet3 for the projects shown in column E. g. . Now, you can see that in the AVERAGEIFS function, cell range D5:F13 is selected as average_range. 1 Using AVERAGE and AVERAGEIF Functions. To be honest, if you're only going to have 1, 2, 3 or 4 in that column, then you could easily use: =AVERAGEIF(F4:F39,">=2",D4. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],. This way, other members can see what has already been done in regards to a. Another issueis that some rows not all of the columns in reaction [Hit 1]:reaction [Hit 5] are populated and the blanks should not be included in the average. This is the syntax of the AVERAGEIFS function. Alternatively have a look at the AGGREGATE function, which can be used as an AVERAGE function (function #1) but with options to ignore certain errors. Rajesh Sinha. ) – Mrig. Array formula 1: find value with two or multiple criteria in Excel. 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. Averaging a range. So AVERAGEIFS won't work here, because it only works with a single row or a single column, where it can pair off the values with the conditions. Jul 9, 2021 at 8:07. AVERAGEIF function for multiple columns. The function was introduced in Excel 2007. Method-3: Averaging Cells That Match Text Exactly. Thanks for the quick reply. I'd like to calculate the average duration per TestName (column A) between the 2 timestamps (B and C) in another sheet. I will show you how to average specific numbers in. Averageifs using OR function. The answer should be 4. In this short tutorial video, you'll learn how to find the average sales from a data set, based upon two different criteria. 5. The take-away is: AVERAGEIFS returns the correct value in D2, despite the #N/A errors in the ranges. 00, the price for a Medium Blue Hoodie. I'm guessing this is probably why I'm getting errors. Our next example is based on a situation when we have blanks in multiple columns. g. 2. so on so forth. AVERAGE using multiple criteria from multiple columns. 3. Average for multiple criteria in one column. You can use AVERAGEIFS formula for multiple conditions. One approach is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. YES, that completely helped. Use AVG and COLLECT: =AVG(COLLECT({Column to Avg}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"))AVERAGEIF (or AVERAGEIFS) won’t handle your ranges being different sizes; one is multiple columns wide, the other just one. which is the call durations column in the table: =AVERAGEIFS(data. [like excel =AVERAGEIFS(average_range, criteria_range1, criteria1,. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. 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. criteria_range1 is required. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory,. Average Based on Multiple Criteria on a Single Column. 3 B 213. The. g. 0 Excel: averageif function for more than one cell. Sub Averageif_Function() Range(" E2") = WorksheetFunction. The input in columm A is time. This is the syntax of the AVERAGEIFS function. Breaking my head over this, time to look for help :(I have a sheet with raw data, as illustrated below. 2. I should have remembered the use of the INDIRECT function to mash multiple columns together from your and other people’s past posts. Issue - How can I find the daily average of Group A for the given dates, given that Group A are in two columns (they can't be combined as there are multiple sub groups) excel; excel-formula;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. . Cross posted multiple sites. I have tried the following formulas. =SUM (ROW (A1:A4)) and press CTRL+SHIFT+ENTER from within the formula bar to convert this to an array formula. Sample Usage. AVERAGEIFS takes care of multiple conditions, but single average_range that in AVERAGEIFS should be the same size as conditions_range. AVERAGEIF function on different cells in different sheets. It allows you to average data in one range of cells where the data in another range matches a certain criteria. This help content & information General Help Center experience. AVERAGEIFS evaluates all the conditions row by row and no row will meet both those conditions. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). 33, or how many Man in China which should average 2. Specify the range for the average C2:C13 (the Defense values) Type , Specify the range for the first condition B2:B13 (the Type 1 values) Type , Specify the criteria (the cell F3, which has the value "Grass")If you then want to restrict which rows are included in the average depending on another column, you use that column in an AVERAGEIFS function along with the value you want to use to restrict in the third parameter. Range 3}, {Butler - PROJECT MASTER -… Help with. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. Formula. =AVERAGEIF (B:B,F1,C:C)Hi Creative999, AVERAGEIFS doesn't have an OR option. So, we get the sum: SUM (Store1:Store3!C6) And then we use the FREQUENCY function (s) to get the count of non-zero numbers. Excel AVERAGEIFS function can be used when you want to get the average (arithmetic mean) of all the cells in a range that meets multiple criteria. 0. I want to sum (or average, but let's stay with sum for this question) the quantities based on top 5 revenue-generating products based on category,. 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. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. To get an average of data in a column with multiple criteria the following formulae may be used. So you just need to add one more criteria for BH US Agent Web form. That’s where the AVERAGEIF function comes into play. For example, one formula that is in use: =AVERAGEIFS (K:K,C:C, ">=01/01/2021", C:C, "<=1/31/2021") This formula works exactly the way I want, for the data specifically only in column K. It is because A and B are merged columns in this area. Just keep in mind that parameter orders are slightly different from AVERAGEIF. The AVERAGEIF function in Google Sheets has the following syntax: =AVERAGEIF(criteria_range, criterion,[average_range]) Let us break down its arguments. 2. Sample usage. Aug 2, 2017 at 10:41. Applying AVERAGE and FILTER Functions Applying AVERAGE and FILTER functions is one of the smartest ways to calculate. 0. AVERAGEIFS: Returns the average of a range depending on multiple criteria. From the selected column, it will search the lookup value to calculate the average of these values. Cross posted multiple sites. AVERAGEIF Google Sheets Syntax. To exclude can use “<>#N/A. It can also be solved. We can use AVERAGEIFS to solve this problem by excluding. Mr Excel Questions 3. Let’s follow the instructions below to learn! Steps: First of all, merge cells E5 to E15. The excel AVERAGEIFS function is used to find the average of values in excel where more than one (multiple) conditions satisfy. 5. 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. SUMPRODUCT is one of the most useful functions that can extract data or do complex calculations by scrutinizing all mentioned criteria along rows & columns from an array. It is the first range that is evaluated. As a worksheet function, the AVERAGEIFS. 1. "<1000") Select the range of cells you want to average. AVERAGEIFS / SUMIF with multiple criteria. So all rows with the same combination of. =count. Otherwise it increases the value of the previous cell by 1. We know that an average is a sum divided by a count. Matching and returning values to populate column. AverageIFs multiple columns when meets multiple criteria in one column. Add another pair of parameters, one which gives the range of the names and another which gives the. 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. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. 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. You can add as many criteria_range,criteria in AVERAGEIFS. Excel: averageif function for more than one cell. This function differs from AVERAGEIF in that it allows you to specify multiple criteria that indicate which cells to average. . We covered all possible comparison operators in detail when discussing Excel SUMIF function, the same operators can be used in SUMIFS criteria. 2. Example 2: Using AVERAGEIFS for Multiple Columns. Joined Feb 1, 2021. Many more great Excel tutorials linked below:sure to watch my other Excel tutorial vid. I am struggling to work out an average in a table with two conditions, I have tried averageifs and sumproduct and failed, please help. Sorted by: 1. A not so average solution to an uncommon problem in Excel. I am trying to use the following formula: =AVERAGEIF(P30:P67;1;AQ30:AS67) So, I need to average on multiple columns, but. Joined Dec 15, 2005 Messages 1,227 Office Version. The formula in E2 is either, =SUMPRODUCT ( (C2:C8)* (B2:B8=F1:G1))/SUMPRODUCT (-- (B2:B8=F1:G1)) Unfortunately, no, it will not help. 2. if column G is the sum of columns A and B and column H is the sum of columns C and D you could use this version3. Fill one cell depending on value of another. UPDATE. New column contains the formula IF (OR (AND (ColA >= Date1, ColB <=Date2), AND (ColA >= Date3, ColB <= Date4)),TargetCol,"") This will return the value to. In the opening Advanced Combine Rows dialog box, (1) Select the column you will search for lookup value, and click the Primary Key; (2) Select the column where you will average vlookup findings, and click Calculate. I currently use a SUBTOTAL function to either sum, count or average a bunch of cells in a range. Calculate average of numbers in a column using column number. Excel Finding Average value based. Recall the formula that we used to calculate the average difference between the two columns: =AVERAGE (B2:B11 - C2:C11) This formula works by first calculating the difference between corresponding values in the ranges B2:B11 and C2:C11. One or more cells to average, including numbers or names, arrays, or references that contain numbers. criteria_column1 – The. I am trying to calculate average based on multiple column. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. It works fine if I only have one of the D column criteria (either one of them), but not with both. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. I am trying to average multiple columns (ignoring zeros and blank cells). In the UI, open the Advanced Editor. It can take arguments where the first argument is the range column in which the average needs to. 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'm wondering if there is a way to nest a vlookup or index match or anything like that in the AVERAGEIFS that read the criteria column heading and criteria in a cell (or 2 if they need to be separated) to be added to the AVERAGEIFS. this formula finds the average of the values $223,000 and $125,000 (from cells D2 and D3). If you have a prior version of Excel let me know and I can write a new array formula to do the same thing. 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. AVERAGEIFS Multiple Tables. criteria_range1 is required. When say conditional average, we think of Averageif, but in this sc. In this case, the AVERAGEIFS function comes as a savior. 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. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. =AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"}) There are more ranges, but the coding for those is fine. Only works with number, date, time, datetime, datetimezone and duration values. For example, if you want to calculate the average of only the numbers greater than zero and lesser than 100, you can use the formula and specify the two criteria. I'd just have to order those fields to get the result I want. The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria. Criteria is the condition to apply, along with any logical operators that are needed. criterion: The pattern or test to apply to `criteria_column`. I have to find average number of days where Status = new or open, Severity = 4, and Area = Apps. I used the TODAY function to indicate 30. Specifying Multiple Range. First, create a column to count how often a record appears on the data. stack the 2 ids and 2 weights columns to create dataframe with 1 ids and 1 weight column. 0. Example 1: To determine the average amount of products that are sold in the north or south use the following formula: =(AVERAGEIFS(B2:B18, A2:A18,. Description Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. George has a worksheet that includes dates (in column A) and values associated with those dates (in column B). Follow. I discovered the QUERY function and 'Google Visualization API Query Language'. Sample Usage. Type an = sign, AVERAGEIF, opening parenthesis (in this example, we are going to evaluate and average the same range of cells, C2 through C5, in the Sales column), comma, then we type the. For example, to find an average of sales in B3:B15 that are greater than $120, the formula is: =AVERAGEIF (B3:B15, ">120") To average based on another cell, you define all 3 arguments: range (cells to check against the condition), criteria (the condition) and average_range (cells to calculate). 0. CountIF and AverageIF a column. Average cells that contain errors or zeros. This is achieved through the use of the Excel AVERAGE function. 08-12-2022 12:38 AM. Like SUMIFS, AVERAGEIFS will allow us to average rows or columns in a table that satisfy multiple specified. Result. The first formula is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. I need to average cells which are labeled in a different column. Trying to run an AVERAGEIFS function across a date variable AND either criteria 1 OR criteria 2 out of a second range. g. Note that, we have changed the column 1 title from Month to Month Number so as to include the numbers inside the formula. The Excel AVERAGEIFS function returns the average of all numbers in a specified range based on multiple criteria. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. I'm sure I'm missing something obvious here, but just to confirm, B3 in this case is an integer (700), column K in the relevant table is the correct reference, and includes figures greater than 700, column G in the table is the correct reference and includes multiple values that are greater than zero, and the formula: Returns the average of a data column depending on multiple criteria. conlan New Member. This particular example will calculate the average. To perform a dynamic two-way average with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill ranges returned by UNIQUE. FORMULA. UPDATE2: I'm including another screenshot. In this example only the rows where the item is. Excel begin AverageIF function. 4. I have about 8 other columns of Data to be included What i need is a formula that. conlan New Member. The article below explains two approaches. AVERAGEIFS works even if there are blank or text values in the measured column.