I have a tab with a table. Therefore the cells in the range are used to calculate the average. 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. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta (if yellow) + counta (if red)). In such cases, you can use the AVERAGEIF and AVERAGEIFS functions. Where Column B contains the numbers being averaged, and column A is your zones. To write a longer formula that gives you the same result,. AverageIFs multiple columns when meets multiple criteria in one column. Finally, we'll take a look at Excel's AVERAGEIFS function. YES, that completely helped. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. I'd like. When the criterion is a number value, simply enter the. 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. 7. 1. Row 3: 98 – 102= -4. 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. 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). The following examples show how to use each formula in practice. average_range is required. I used the AVERAGEIFS command but I recieve a message that "excel the formula you typed contains an error". Well, either SUMPRODUCT or array formula. Phrased another way, you could say it like this: "calculate the average of columns J and K (from the 1st screenshot) for all the instances where the following conditions are met: column K is less than or equal to 1638, column M is "1". In our following & new dataset, the sales of computer devices of different brands are presently based on the months. To get an average of data in a column with multiple criteria the following formulae may be used. 0. Then I can use =AverageIfs(B:B,[month helper range],1,[year helper range],2007). 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. One or more cells to average, including numbers or names, arrays, or references that contain numbers. 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. Excel AverageIf to get total average of multiple rows from a table. It is the first range that is evaluated. Criteria is the condition to apply, along with any logical operators that are needed. I used the TODAY function to indicate 30. The syntax for the AVERAGEIF function is:What I want to do is have that formula reference the heading and then lookup the appropriate column in the data table to apply the averageif criteria to. Select a blank cell, enter formula =AVERAGE (IF (MOD (COLUMN (C2:G2)-COLUMN (C2),2)=0,IF (C2:G2,C2:G2))) into the Formula Bar, and then press the Ctrl + Shift + Enter keys simultaneously. 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. – Gandalf96. As you have found, AVERAGEIF does not work across multiple sheets; nor do COUNTIF, COUNTIFS, SUMIF etc. Next, C5:C14 is the criteri_range1 argument which refers to the Section column and the C17 is the criteria1. This cell range contains multiple columns. 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. The result is the average of values in group "A" for all three months of data. Another difference is the positioning of arguments. Countif with 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. In this case, the AVERAGEIFS function comes as a savior. In this video show you how to calculate the average values across multiple columns with a filterin. By trubertiam in forum Excel Formulas & Functions Replies: 7AVERAGEIF(table_name!score, “>”, B2) Syntax. . If the list is empty null is returned. =AVERAGEIF (Item:Item, "Shirt", [Quantity Ordered]: [Quantity Ordered]) Description. 1. Any record appearing more than once (>1) is considered a duplicate. I cannot perform this function using AVERAGEIFS since the ranges are not the same size, spanning multiple columns. Matching and returning values to populate column. 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. A good way to do this is to use the AVERAGEIFS function, which can calculate averages based on one or more criteria. But when it comes to comparing two or more arrays, especially with multiple criteria, SUMPRODUCT is the most effective, if not the only, solution. Let’s determine the average for sales that are greater than $60000. 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. I have a spreadsheet with a col of phases (Phase 1, Phase 2, etc. 0. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. Then your calculations table would be quite. AverageIFs multiple columns when meets multiple criteria in one column. SYNTAX. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. See the. Range 3}, {Butler - PROJECT MASTER -… Help with. Average cells that contain errors or zeros. Green 10. This is the range that contains numbers to average. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. 0 Excel: averageif function for more than one cell. The first formula simply confirms the second formula. 5. e. One is Name Box and another is Formula Box. In Excel, we often need to calculate the average of multiple columns based on multiple conditions. This sort of ambiguous situation it can't compute. Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. However, the result needs to ignore any zeros that appear in the data. Using Averageif for 2 criteria in an Array in. 3. g. kckliodna; Aug 25, 2023; Excel Questions; Replies 3 Views 452. Returns the average (arithmetic mean) of all cells that meet multiple criteria. Average for multiple criteria in one. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. It returns a number that represents the average (arithmetic mean) of the arguments. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub Returns the average of a data column depending on multiple criteria. 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. so on so forth. Syntax. To exclude can use “<>#N/A. If done correctly then excel will put {} around the formula. Here is a screenshot to clarify. 0 How to use Averageifs to Calculate average with multiple criteria and ignore text. RangeForCriteria1: C2 to C8. You are looking for the AVERAGEIF function seen here. g. The AVERAGEIF function is one of the older functions used in spreadsheets. Concept is below. This is the syntax of the AVERAGEIFS function. Criteria_range1 is required, subsequent criteria_ranges are optional. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. One approach would be to do a Group By on Group using Average as your aggregate. In. The result returns 7 (average value). FORMULA. 0. Suppose you have a table listing the scores of two tests. 1. Cross posted multiple sites. 0. Average for multiple criteria in one column. 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 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. Formula Breakdown: AVERAGEIFS(D5:D14,C5:C14,C17,D5:D14,”>=”&85) → finds average for the cells specified by a given set of conditions or criteria. You can use the following methods to write AVERAGEIF and AVERAGEIFS functions using VBA in Excel: Method 1: AVERAGEIF Function in VBA. The average function evaluates the zero but ignores the blank cell and the. If you want to also exclude blank cells, you should use the AVERAGEIFS function. 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. Description. Average for multiple criteria in one column. It is important not to use simply AVERAGE function but divide SUM by SUM (COUNT) instead. Blank and text values are ignored. 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. But as demonstrated below, they do not. Thus, 5 columns are reserved for the category of a given record. Rather than spanning dates across the columns on a single row, you should use a single date column. e. I have used your code of calculating rolling average but. 1. Unlike some functions, e. 0. The VSTACK function accepts 1 or. I've also tried inserting a new row below the dates (i. 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. Two-column example. AverageIFs multiple columns when meets multiple criteria in one column. The above formula. That didn't work either. The AVERAGEIF formula within the Google Sheets application. I tried the following: AVERAGEIF(A:A,"<>INDIRECT(""A""&(ROW()-1))", B:B) but that doesn't seem to work and it takes all rows. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. Returns the average (arithmetic mean) of all cells that meet multiple criteria. Type "," and then enter the criteria you want to use (e. A couple of ways to overcome this. This function can be used to know the average balance in the inactive bank accounts with an updated KYC. e. 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:. Initially, the AVERAGEIF function allowed users to calculate the average of values after checking for one criterion only, but this function now allows you to check for multiple. . The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. Note the lookup_value in XLOOKUP is 1 since the. It works fine if I only have one of the D column criteria (either one of them), but not with both. Suppose we would like to calculate the average value in the points column for players who are on the Mavs team. g. Method-4: Find the Average of Cells That Match Text Partially. ; The function treats the cell as a ‘0’ value if any cell in the criteria range is empty. Search. 0. "<1000") Select the range of cells you want to average. You can use AVERAGEIFS formula for multiple conditions. In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. One or more cells to average, including numbers or names, arrays, or references that contain numbers. Perhaps try something like this: Code: Sub example () Dim av As Double av = Application. To do that you could calculate average by yourself. 0. You need the AVERAGEIFS formula that will take a column of values and multiple conditions on whether to include the value of a particular row in the calculation. I am trying to sum a total with multiple criteria (a. Read below for details. 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. Define a range name to non-contiguous ranges. Windows; Dec 6, 2011. The take-away is: AVERAGEIFS returns the correct value in D2, despite the #N/A errors in the ranges. = AVERAGEIF ( A3:C3, "<>0" ) Since the function ignores the zero value in cell B3, the average of the remaining two cells is 5 ( (4+6)/2 = 10). 2. For example, it calculates: Row 1: 100 – 90 = 10. Returns the average value for the items in the list, list. Description. As a worksheet function, the AVERAGEIFS. However, you can use the following formula as a workaround: = (SUM (SUMIF (A2:A11,G2,B2:B11),SUMIF (D2:D11,G2,E2:E11))/SUM (COUNTIF (A2:A11,G2),COUNTIF (D2:D11,G2))) This particular formula finds the average of the values in the. Thread starter conlan; Start date Jul 23, 2014; C. It also supports the specification of multiple columns to compute. If I understood correctly, you are trying to choose dates before 4/1/14 OR after 8/30/14. . Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor. Then select the merged cells. The issue arises when there are multiple columns with same group as Averageif takes the first column only. In the Combine Worksheets – Step 1 of 3 wizard, check the Consolidate and calculate values across multiple workbooks into one worksheet. =AVERAGEIF(A1:A50,">0") This function only includes in the average those cells that contain values greater than zero. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. By thatguytg53 in forum Excel Formulas & Functions Replies: 11 Last Post: 03-11-2014, 06:17 PM. Specifying Multiple Range. 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 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 version To calculate an average when corresponding cells are not blank, you can use the AVERAGEIFS function. 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. In your scenario, you could use the following formula to get the average: =AVERAGE (IF (A2:A200=1,B2:C200)) ENTERED AS AN ARRAY FORMULA using cntrl + shift + enter. 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. 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 calculate an average by group with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill range returned by UNIQUE. e. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. 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. Enter the following formula in S2: =--(R2>0)Average for Multiple Criteria. In this video show you how to calculate the average values across multiple columns with a filterin. Excel: averageif function for more than one cell. It is the first range that is evaluated. Since your. Does that make sense?Range("C1:C676") = Application. How do I `SUM` by multiple columns in Excel. I have tried =Average (IF ( (A:A="Apps")* (B:B="4")* (C:C="new")+ (C:C="open. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. What it Returns. One approach is based on the AVERAGEIFS function,. average_range is required. AVERAGEIF with multiple criteria. I have manually averaged some of the formulas to double check them and they are not computing accurately. Good morning all, I am trying to get the average of a column if the date in a different column is between 2 dates. 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"). Excel begin AverageIF function. Sample usage. If not included, `criteria_column` is used for the average instead. AVERAGE using multiple criteria from multiple columns. 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. Sumif range of criteria. Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2) In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'. A not so average solution to an uncommon problem in Excel. 0. CountIF and AverageIF a column. AVERAGE using multiple criteria from. If you need to add two criteria, for example the average quantity of shirts that Joe orders, use a combination of AVG and. Then we create a COUNTIF function to count the number of records that appear more than once: =count. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. Description Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. One approach is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. 1. First the Formula you have will not return the true average of the found criteria but the average of the average. Example 1: Average If Not Blank (One Column)Example AVERAGEIFS function, step by step: Select the cell H3. But the columns above are the only ones that will need to be in the formula. 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. 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. 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. Would have used AverageIfs in excel for this calculation. 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. In this instance, select B2 through B16, to identify the Age column as the range. 00, the price for a Medium Blue Hoodie. Multiple AVERAGEIF from a List Hi all, I'm trying to average specific values from a list I have. Second part SUMPRODUCT ( (C3:M17<>"")* (A3:A17=750)* (C1:M1=15)) gives. Here, the average is calculated on the basis of two criteria- Employees living in Mumbai and employees whose Age > 50. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. The syntax for AVERAGEIF is: =AVERAGEIF (Range,Criteria,Average_range). The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not. 97. Average multiple values from multiple columns in Excel. Attention - ExcelForum Rules have been updated as of August 2023. The AVERAGEIFS uses the following syntax: average_range, criteria_range1, criteria1, criteria_range2, criteria2, etc. I came across a scenario today however which stumped me, and was hoping that perhaps someone on here might have a solution! I am attempting to get an average of multiple non consecutive cells, ignoring anything that is equal to '0'. g. Let’s follow the instructions below to learn! Steps: First of all, merge cells E5 to E15. I'd just have to order those fields to get the result I want. Returns the average of a data column depending on multiple criteria. Problems averaging multiple columns using AVERAGEIF and AVERAGEIFS . Like SUMIFS, AVERAGEIFS will allow us to average rows or columns in a table that satisfy multiple specified. It ignores the “Sick” value in cell B6 since the AVERAGEIF function does not assign numeric values to text. 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. Hello! I am new to this forum, but have been using Excel for some time now. By jeroenft in forum Excel General Replies: 4 Last Post: 02-03-2015, 04:58 PM [SOLVED] AverageIF using multiple criteria in different columns in multiple worksheets. 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. Averageifs with OR criteria. AVERAGEIF with multiple criteria. e. Step 3: Supply the criterion. 3. How to use AVERAGEIF in Google Sheets, only addressing columns with a specific text. columns(2). It can also be solved. You can add as many criteria_range,criteria in AVERAGEIFS. The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not blank. Add a comment. Jul 23, 2014 #1 I am trying to find the average for multiple columns using a criteria from another column. 0. 2. Yes, separately the two formulas work as intended for the way they are written, however I am wanting to combine them. You are comparing three whole columns to a range 5 columns wide and 8 rows high. the formula that builds the average column I want to be similar to how excel's AVERAGEIFS folrmula works. Averageifs for multiple criteria. Specify the range to check against the 1st condition (B3:B15 - items). 2. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. The generic formula of Excel IF with two or more conditions is this: IF (AND ( condition1, condition2,. 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. Let us see how to apply this function with multiple ranges. 5. There are two fields in the Insert Calculated Field dialog box. The VBA help does provide some detailed. 1. Type =AVERAGEIFS. Average. 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. I've tried to do an averageif formula and all sorts but it won't work with the categories. 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. Finally, find out the average of the given range of cells. It is one or more cells to average. He would like to calculate the average of all the values for a given month in a given year. 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. Sorted by: 1. The worksheet includes values for the last several years. If you select cell D8 of the example, the complete function appears in the formula bar above the worksheet. which is the call durations column in the table: =AVERAGEIFS(data. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. Under each month I have a reported lab value. 1 Answer. g. This can be accomplished by using multiple AVERAGEIFS function to the formula. I have no problem running this on a single column of data but get lost when trying to apply the average to multiple columns. 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). in this example Temprange. Excel AVERAGEIFS Function. Aug 2, 2017 at 10:41. 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. Averaging a range. You can apply the AVERAGEIFS function on a single column to calculate the average. . However, you can use the following formula as a. 365; Platform. 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. (2) Work out the count for each column separately and total them up. Example of Data Table being used. But from what I have read so far in the forum, it's not the right function to get where I want to. After this, you. =AVERAGEIFS (G:G,G:G,">0",A:A,"PHA") Spot on, Thank you. AverageIFs multiple columns when meets multiple criteria in one column. The first formula is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. 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. Sub Averageif_Function() Range(" E2") = WorksheetFunction. average_range: (Required) The range of cells you want to average from. Now follow the instructions at the top of that screen. UPDATE2: I'm including another screenshot. ) – Mrig. How to Calculate Average of Multiple Columns Based on Single Condition 1. 0. Add a comment | 2 Answers Sorted by: Reset to. I only added some of January's data and not all the months for the post. 9. The AverageIfs method measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. Sample Usage. To look up several contiguous columns, you can use the INDEX function in an array formula to return multiple columns at once (use 0 as the column number). 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". Sample usage. However, the result needs to ignore any zeros that appear in the data. AVERAGEIFS() requires that all the ranges must be the same size and shape. Originally Posted by daddylonglegs. Finally, we'll take a look at Excel's AVERAGEIFS function. Group Average A 113. Type "=AVERAGEIF (" into a blank cell. Follow. ) Otherwise, when you propogate down for subsequent row averages,. 2. To define criteria, AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be. 0. The excel AVERAGEIFS function is used to find the average of values in excel where more than one (multiple) conditions satisfy. We can create the following macro to perform this AVERAGEIF function: Sub Averageif_Function() Range(" E2") = WorksheetFunction. As Jeff pointed out, the function will silently resize your ranges to suit that restriction - so for example if you typed:. 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. Calculating Average by multiple columns. 09-17-2012, 03:12 PM #5. AVERAGEIF with multiple criteria. Example 1: AVERAGEIFS with One Character Column. Edit: In the mean time, I have created two helper columns, that are just the Month() and Year() of each row of data. In this instance I want to exclude the zero value from the calculation. No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. I have tried using the OR function, and tried the curly brackets, but both give me errors. If you have a prior version of Excel let me know and I can write a new array formula to do the same thing. Click Kutools Plus > Combine to open the Combine Worksheets wizard. Does this help? Click to expand. Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2) In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'. It can take arguments where the first argument is the range column in which the average needs to. 2. Yep! Averageif is capable of handling multiple text conditions in one column. AVERAGEIF with multiple criteria: AVERAGEIFS. 2 Apply AVERAGEIFS Function for Multiple Columns. I am trying to sum a total with multiple criteria (a name descriptor and a date).