No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. However, the result needs to ignore any zeros that appear in the data. This can be accomplished by using multiple AVERAGEIFS function to the formula. 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 exclude can use “<>#N/A. Here is the same formula with an adjusted range to C2:N22. Basically, the AVERAGEIF function estimates the average based on a single criterion. Array formula 1: find value with two or multiple criteria in Excel. In this instance, select B2 through B16, to identify the Age column as the range. criteria_column1 – The. See screenshot: 2. Share. Let us see how to apply this function with multiple ranges. in C2 it needs to calculate the average of F2-F6 since E2-E6 is equal to, or between 140 and 142 (Interval in cells A-B). We can create the following macro to perform this AVERAGEIF function: Sub Averageif_Function() Range(" E2") = WorksheetFunction. The formula I've tried is: =AVERAGEIF (B7:B131,LARGE (B7:B131, {1,2,3}),G7:G131) The problem is it does not average the three, but rather it only displays the column G equivalent to the largest number in column B. It is my understanding an averageifs will not work because it will not do multiple criteria in the same column. Averaging a range. However, the result needs to ignore any zeros that appear in the data. That’s my simple answer to the above question. Hi All, I am trying to calculate average based on multiple column. 2. Easiest way I’d suggest is use BS40 to average the row, fill to BS100, then simple =AVERAGEIFS(BS40:BS100,O40:O100,A1) ReplyThe AVERAGEIFS function syntax has the following arguments: Average_range Required. In this case, we're checking cells C2 to C17, for all values greater than $100. Description. What it Returns. g. By thatguytg53 in forum Excel Formulas & Functions Replies: 11 Last Post: 03-11-2014, 06:17 PM. This article explains how to. I want the average of 'column b' data that corresponds to 'red' and 'yellow'. A not so average solution to an uncommon problem in Excel. 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. We can use the following formula to calculate the average value in the Points column where the Team column is equal to “Mavs”: =AVERAGEIFS(C2:C11, A2:A11, "Mavs") The following screenshot shows how to use this formula in practice: The average value in the Points column where the Team column. To average numbers based on multiple criteria, you can use the AVERAGEIFS function. AVERAGEIFS Multiple Tables. Average cells that contain errors or zeros. 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. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. 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. Criteria Required. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. 2. Share. It can also be solved. e. conlan New Member. In this video show you how to calculate the average values across multiple columns with a filterin. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. In this example, we want to combine the cell ranges A2:B14 and D2:E14. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. The criteria values are entered on the. Otherwise it increases the value of the previous cell by 1. By default, the AVERAGEIFS function in Excel uses AND logic to calculate the average value of cells that meet multiple conditions. The example is the column is Apples and the next columns are Red Apples, then yellow apples and the Apples column is taking an. microsoft-excel; worksheet-function; Share. I've also tried inserting a new row below the dates (i. =AVERAGEIFS(B:B,A:A,"=Apple",A:A,"=Pear",C:C,"=Yes"). 0. Well, either SUMPRODUCT or array formula. Using Averageif for 2 criteria in an Array in. Then select the merged cells. Now it correctly returns 10 same as =SUM (1,2,3,4) You should also avoid Average of Average as it might lead to erroneous results. One or more cells to average, including numbers or names, arrays, or references that contain numbers. As Jeff pointed out, the function will silently resize your ranges to suit that restriction - so for example if you typed:. Breaking my head over this, time to look for help :(I have a sheet with raw data, as illustrated below. Select the columns or rows you want to compare. For example, it calculates: Row 1: 100 – 90 = 10. 2. The issue arises when there are multiple columns with same group as Averageif takes the first column only. Excel: Averageifs from a table with columns. 0. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. Excel - Apply IF on all elements of the column before doing an Average. AverageIf(Range("F5:F30"), "Savings",. The first parameter is the , which is A2:A10. The input in columm A is time. One or more cells to average, including numbers or names, arrays, or references that contain numbers. 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. The AVERAGEIFS function works like AVERAGEIF, except it is designed to apply multiple criteria. Not quite sure how to describe it without context, so here's what I'm working with (minus 100+ rows of data): I've made the function look in column D for values of "W" and column C for values of "Cache". In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. Suppose you have a table listing the scores of two tests. 1. Joined Jul 23, 2014 Messages 2. g. To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). Like SUMIFS, AVERAGEIFS will allow us to average rows or columns in a table that satisfy multiple specified conditions. 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. Criteria_range1 is required, subsequent criteria_ranges are optional. Suppose we would like to calculate the average value in the points column for players who are on the Mavs team. 1. When say conditional average, we think of Averageif, but in this sc. Type "=AVERAGEIF (" into a blank cell. Use SUMPRODUCT:AVERAGEIFS returns the average of all cells that meet multiple criteria. The formula: =1/ (critRng1=TRANSPOSE (crit1))*1/ (critRng2>=DATE1)*1/ (critRng2<=DATE2)*avgRng. I want to calculate the average of the last column (SLA in days) for the lines. AverageIfs(Sheets(modelName). Coachcasa New Member. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. NOTE: AVERAGEIF is only available in Excel 2007 and later. "<1000") Select the range of cells you want to average. 1. Formula to create a cartesian product from two data sets in a spilled range. Formula. 0. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). I tried this but failed: =AVERAGEIF(range1; criteria 1; range 2; criteria 2; average range). The first formula is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. average_range is required. I am trying to use the following formula: =AVERAGEIF(P30:P67;1;AQ30:AS67) So, I need to average on multiple columns, but. =AVERAGE (IF ( (C2:C5)* ( (D2:D5="US")+ (D2:D5="Germany")),B2:B5)) After array-entering the formula, the formula bar will show. 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. =AVERAGEIF(A1:A50,">0") This function only includes in the average those cells that contain values greater than zero. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. averageifs w/ multiple average ranges in different columns. AverageIfs(TempRange. So how would I be able to take out the average of a table using dynamic columns and rows. Excel: how to. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. The mistake in the formula is that it only takes one argument for the range. Method-2: Finding Average If Cell Contains Text (Multiple Text Criteria) 2. 97. =AVERAGEIF (Item:Item, "Shirt", [Quantity Ordered]: [Quantity Ordered]) Description. 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,. Example 2Here's an example of an AVERAGEIF formula with a date: =AVERAGEIF ( [Date Column]: [Date Column], >TODAY (-30), [Data Column]: [Data Column]) This will average the cells in the Data Column that correspond with a row where the Date in the Date Column is greater than 30 days ago. In Excel 2016+ (may be different in earlier versions): select some cell within the data table. Add another pair of parameters, one which gives the range of the names and another which gives the. Perhaps try something like this: Code: Sub example () Dim av As Double av = Application. Under each month I have a reported lab value. You could perhaps use SUMPRODUCT to get the sum of all the columns with the given restrictions, then COUNTIF to get the number of columns with the given restriction: =SUMPRODUCT (A2:D11*ISNUMBER (SEARCH ("Yes",A1:D1)))/COUNTIF (A1:D1,"*Yes*") SUMPRODUCT should return 77+88 and COUNTIF should return 2. 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. The generic formula of Excel IF with two or more conditions is this: IF (AND ( condition1, condition2,. Is there a way to do. Say, you want to average the profits for the products whose sales quantity is greater than or equal to 100 and profit is greater than or equal to 50,000. To start off, let's calculate a straight average with the AVERAGE function for reference. 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. Data / Get & Transform / From Table/Range. To begin this method, double-click on cell D10 and insert the formula below. Thread starter mgirvin; Start date Dec 6, 2011; M. The function was introduced in Excel 2007. Excel AverageIf to get total average of multiple rows from a table. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true. the formula that builds the average column I want to be similar to how excel's AVERAGEIFS folrmula works. I'm trying to make use of Averageifs with multiple criterias in multiple columns. I've tried several 'averageif' formulas with 'and' and 'or'. As the formula is copied down, it returns total hours in Sheet1, Sheet2, and Sheet3 for the projects shown in column E. See Efficient Spreadsheet Design. Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) Hot Network Questions What is the earliest known historical reference to Tutankhamun?2. Cell C3 sums all the numbers in range A1:A5:. Average (1,2,3) is not same as Average (Average. Let’s use the procedure discussed in the following section. The AVERAGEIF function is one of the older functions used in spreadsheets. e. You can use the following methods to write AVERAGEIF and AVERAGEIFS functions using VBA in Excel: Method 1: AVERAGEIF Function in VBA. I am trying to sum a total with multiple criteria (a. Select Done to complete the function. Result. Next, C5:C14 is the criteri_range1 argument which refers to the Section column and the C17 is the criteria1. We can use the following formula to calculate the average value in the Points column where the Team column is equal to “Mavs”: =AVERAGEIFS(C2:C11, A2:A11, "Mavs") The following screenshot shows how to use this formula in practice: The average value in the Points. FORMULA. )] I hope I was able to explain what I want. Here, D5:D14 is the average_range argument which is the Physics column. criteria_range1 is required. When I adjusted my formula to C2:N22 I get the result of 4. . Excel AVERAGEIFS Function. The formula I have now works fine but as I add more columns and the data table that may be out of order it would be much better if I was able to incorporate a lookup or match into the. Average. 0 How to use Averageifs to Calculate average with multiple criteria and ignore text. In both the VBA and Excel examples the formula averages all of the numbers in columns C and D. criterion is the condition to be tested. The AVERAGEIFS Excel function helps users provide multiple criteria for a given dataset, finds the cells that fulfill all the criteria, and calculates the average of the respective cell values. One approach would be to do a Group By on Group using Average as your aggregate. 2. – Spinner. The function was introduced in Excel 2007. AVERAGEIFS (C2:C8,B2:B8,”Mumbai”,C2:C8,”>50″) example. Viewed 505 times. When the criterion is a number value, simply enter the. Average multiple values from multiple columns in Excel. Blank and text values are ignored. Name Prior. AVERAGEIF with multiple criteria. 2. The second formula is based on the FILTER function and the AVERAGE function. Long story short, I'm trying to average multiple columns of number data based upon if the column matches criteria. Jul 9, 2021 at 8:07. Excel begin AverageIF function. The issue is, as you can see in the picture, RANGE and AVERAGE_RANGE paramet. In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. About;. The query then averages the values from column C and filters out the rows where the conditions of. 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. About. AVERAGEIF vs AVERAGEIFS While Considering Criteria. I have about 8 other columns of Data to be included What i need is a formula that. The Regexmatch will help us to use multiple text conditions in one column in the Averageif function in Google Sheets. There is a formula that does this for you called AVERAGEIFS. . Because we already have unique departments in column F, we can reference the list directly. RangeForCriteria1: C2 to C8. Criteria Required. All cells in column C (with a value to the left) have this formula: =IF(ISNUMBER(SEARCH(C$7,A1)), B1, "") Cell, C7 just has the word desk typed out so that those formula don't need to be fixed every time you change the search. Would have used AverageIfs in excel for this calculation. – Gandalf96. In this section, we will use. Paste the M-Code below into the window that opens. In the above, the sum of the blue/greens is 23, their count is 5 making the average 4. Using =averageifs with multiple text criteria in different columns. For example, we can compute the average of the amount column, but only include those rows where the customer column is equal. criteria_range1 is required. =SUM (ROW (A1:A4)) and press CTRL+SHIFT+ENTER from within the formula bar to convert this to an array formula. It works fine if I only have one of the D column criteria (either one of them), but not with both. How do i get an average of a column, with multiple conditions herof an OR condition. I am wanting to do an averageifs statement, but it does not seem that is supported. =AVERAGEIF(B2:H11, “<60”) The formula above only takes into account and finds the average of the four highlighted values — 54, 49, 56, and 0. Example 1: AVERAGEIFS with One Character Column. 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. . Which can take in multiple criteria to determine what to average. You require an OR condition since any one row could not be both 1 and 2. Let’s follow the instructions below to learn! Steps: First of all, merge cells E5 to E15. You can use AVERAGEIFS formula for multiple conditions. ) ARGUMENTS. The AVERAGEIF formula within the Google Sheets application. this formula finds the average of the values $223,000 and $125,000 (from cells D2 and D3). Criteria is the condition to apply, along with any logical operators that are needed. CountIF and AverageIF a column. This will do what it looks like you are trying to do with your formula. Columns G & I have text values and Column K is a number value. Blank and text values are ignored. to get the average of every column of the table using a query. The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria. For example lets say your data has 2-2,10-3,100-4 the true average of the 112 numbers would be close to 4, but if you took the average of the 2s, the average of the 3s then the average of the 4s, then averaged those you would get 3. AVERAGEIFS function in Excel is used to find the average from the target range of cells when more than one condition is met. Note. To get column average or mean from pandas DataFrame use either mean() or describe() method. Firstly, select your entire dataset and press the keyboard shortcut. AverageIfs Multiple Criteria In Columns Feb 5, 2014. Now follow the instructions at the top of that screen. A good way to do this is to use the AVERAGEIFS function, which can calculate averages based on one or more criteria. 1. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. . The Excel AVERAGEIFS function returns the average of cells that meet multiple conditions, referred to as criteria. 3 B 213. Is there a simple way to copy the formula over and have the column numbers move four across to average subsequent years?In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. AVERAGEIFS uses all criteria you offer to select a number. Multiple columns. I would like to average the lab values over the months, however, I only want to average them if the value in column cell A is equal to "Y". There are two fields in the Insert Calculated Field dialog box. Count from multiple columns with multiple conditions in Excel. Sample Usage. 2. It is the first range that is evaluated. Use the following formula for G1 and copy down to the bottom of your list in column G. Thread starter conlan; Start date Jul 23, 2014; C. Cross posted multiple sites. While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). 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. We can add as many criteria as needed to determine our final results. AverageIFs multiple columns when meets multiple criteria in one column. The Convert Text to Columns Wizard –. Would have used AverageIfs in excel for this calculation. Meaning the content of the data in the column is an average dependant on multiple other columns in that table. In the UI, open the Advanced Editor. The 2 conditions are mutually exclusive, so you're presenting nothing (null) to the average function (within the AVERAGEIFS). One or more cells to average, including numbers or names, arrays, or references that contain numbers. 3. Sumifs with date difference in range. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. To calculate a conditional average for multiple columns of data, you can use the AVERAGE function with the FILTER function. 0. I have a tab with a table. ) – Mrig. SYNTAX. 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. Rather than spanning dates across the columns on a single row, you should use a single date column. Still an array formula. column_references: The columns which you want to average. AVERAGEIF with multiple criteria. which is the call durations column in the table: =AVERAGEIFS(data. Then I can use =AverageIfs(B:B,[month helper range],1,[year helper range],2007). I want to build a table on the last sheet where I would calculate the average of every price of each product for the entire period of time of the document (several years and thus several sheets). We take a dataset that includes some students,. The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. I'd just have to order those fields to get the result I want. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. Sub Averageif_Function() Range(" E2") = WorksheetFunction. AverageIFs multiple columns when meets multiple criteria in one column. Range("A1")) The problem i'm running into is that the last criteria range does not run through the full column of data in. 2. in C2 it needs to calculate the average of F2-F6 since E2-E6 is equal to, or between 140 and 142 (Interval in cells A-B). 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. 2. 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. Column C = 1 if there is a number in it, AND it is a visible row, otherwise column C. RangeForCriteria1: C2 to C8. 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. Sample Usage. I have tried =Average (IF ( (A:A="Apps")* (B:B="4")* (C:C="new")+ (C:C="open. Since the AVERAGE function will ignore text, we can use IFERROR and AVERAGE to get the answer: To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). so on so forth. 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. For convenience all data is in an Excel Table named data in the range B5:. How to use AVERAGEIF in Google Sheets, only addressing columns with a specific text. Only works with number, date, time, datetime, datetimezone and duration values. I’m trying to get an average of data in column C based on the corresponding time range in column A. So, one thing I'm thinking is that your lookup has no records or you have a mispelling. Any record appearing more than once (>1) is considered a duplicate. You can add as many criteria_range,criteria in AVERAGEIFS. 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. 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. Using the AVERAGEIF function is quite similar to using the SUMIF functions in the cells. Press Enter key. 1. Good morning all, I am trying to get the average of a column if the date in a different column is between 2 dates. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Criteria_range1 is required, subsequent criteria_ranges are optional. 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. averageifs(columnA, columnA, "<3", columnB, "2") EDIT I get a "Type Mismatch" ColumnA and ColumnB are ranges, in if sentences this is what I wantIn AVERAGEIFS function (like SUMIFS and COUNTIFS) all the ranges need to be the same size, so if you want to average 4 columns based on criteria in single columns you need to use an "array formula" like this: =AVERAGE(IF(Sheet1!C2:C7000=C3,IF(Sheet1!J2:J7000="Drivers",IF(Sheet1!K2:N7000<>"",Sheet1!K2:N7000)))). Excel averageif with specific cells. In the example shown, the formula in cell G5 is: =AVERAGEIFS(data[Score],data[Group],F5#) Where data is an Excel Table in the range. The criteria is supplied in pairs: first the range, and then the criteria. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column - The data column to average. In Excel 2007 and later versions, you can use the AVERAGEIFS function to average rows that meet two or more criteria. "A" and "G") may be zero. It returns a number that represents the average (arithmetic mean) of the arguments. In the example shown, the formula in cell G5 is: =AVERAGEIFS (data [Rating],data [Age],F5#,data [Gender],G4#) Where data is an Excel Table based on the. 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. AVERAGEIF With Multiple Criteria. The AVERAGEIFS function will take these criteria and ignore the entire row if there is a blank. The following examples show how to use each formula in practice. I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. Excel: averageif function for more than one cell. 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. A not so average solution to an uncommon problem in Excel. Select the range of cells you want to evaluate. The AVERAGEIFS function in Excel can calculate the average of cells only with values using multiple criteria. The criteria range is data[Group], and the criteria is the spill range: =AVERAGEIFS(data[Score],data[Group],F5#) To calculate a conditional average for multiple columns of data, you can use the AVERAGE function with the FILTER function. Example 1: AVERAGEIF Function in VBA. The generic syntax for AVERAGEIF looks like this: = AVERAGEIF ( range, criteria, average_range) The AVERAGEIF function takes three arguments: range, criteria, and average_range . 2. 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. 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. 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. Make sure to start the formula with Equals Sign. If you need to add two criteria, for example the average quantity of shirts that Joe orders, use a combination of AVG and. I feel like I am missing something simple or easy that is not locking in the specific data I would like to compute. Nov 7, 2021 at 0:27. Formula 2: Average If Not Blank (Multiple Columns) =AVERAGEIFS(C:C, A:A, "<>", B:B, "<>") This formula calculates the average in column C only where the values in column A and B are not blank. Re: averageif across worksheets.