Specify the range to check against the 1st condition (B3:B15 - items). Average Based on Multiple Criteria on a Single Column. 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. A good way to do this is to use the AVERAGEIFS function, which can calculate averages based on one or more criteria. Insert an empty column in column S. 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. So I have 3 columns in this example. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. Use the following formula for G1 and copy down to the bottom of your list in column G. 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. Using the AVERAGEIF function is quite similar to using the SUMIF functions in the cells. 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. Let’s assume you have the below table where we have the number of items in the stock and their selling price. Aug 2, 2017 at 10:41. 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. The. The formula can use any worksheet function and use any fields from the data source. Using a Table (or excluding the header row), avoids that difficulty. to get the average of every column of the table using a query. 1. Can I not put multiple criteria on the same column range in an AVERAGEIFS? If not, could someone please suggest another way of doing this. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. AVERAGEIF vs AVERAGEIFS While Considering Criteria. This cell range contains multiple columns. 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. Add a comment. 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. And the. 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. The article below explains two approaches. AVERAGEIF is only available in Excel 2007+. g. this formula finds the average of the values $223,000 and $125,000 (from cells D2 and D3). Attention - ExcelForum Rules have been updated as of August 2023. And because of this when You are doing Your AVERAGEIFS function it will result in a #DIV/0. so on so forth. Average with multiple conditions. So for example, say I want the average between 09:00:00 and 09:30:01. 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. For example, in column A I have the year, in column B I have the month, and in column C I have the values. The difference between Averageif and Averageifs in Google Sheets is in the criteria used. 1. Sumif range of criteria. 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. Share. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. The AverageIfs method measures central tendency, which is the location of the center of a group of numbers in a statistical distribution. 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. The formula makes use of a virtual array, containing the values of column C and the output of the mmult () function. "A" and "G") may be zero. Excel Finding Average value based. I only added some of January's data and not all the months for the post. This will do what it looks like you are trying to do with your formula. =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. Now, write down the VBA code to calculate the average of multiple ranges. This particular example will calculate the average. One or more cells to average, including numbers or names, arrays, or references that contain numbers. AVERAGEIF function using reference as criteria range. Sample Usage. In this example only the rows where the item is. In the example shown, the formula in H5 is: = AVERAGEIFS ( sales, group,F5, region,G5) where data is an Excel. 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. 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. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). Improve this answer. It is important not to use simply AVERAGE function but divide SUM by SUM (COUNT) instead. I have tried =Average (IF ( (A:A="Apps")* (B:B="4")* (C:C="new")+ (C:C="open. 1. If you instead would like to use the AVERAGEIFS function with OR logic to calculate the average value of cells that meet at least one of multiple conditions, you can use the following syntax:. . 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. 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)). Excel can calculate a sum, average or count of values based on specific criteria by using the SUMIFS, COUNTIFS and AVERAGEIFS functions. Therefore the cells in the range are used to calculate the average. COUNTIF: Returns a conditional count across. 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. The AVERAGEIF Function allows you to average the sum of a range of cells that meet a certain criteria. Both of these ranges contain only one column. Full formula should look like:It will also do that if there previous cell is equal to "". AVERAGEIF Function. 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. 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. average_range is required. in this example Temprange. 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. Sumifs with date difference in range. (A$2:Z$2, rather than simply specifying the range as A2:Z2. 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. Specify the range to check against the 1st condition (B3:B15 - items). 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. In this example, we will demonstrate the use of the AVERAGEIFS function based on multiple criteria. For. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. 1. The FREQUENCY function performs a series of "count if" operations based on the arguments provided. 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. The formulas in D1:D2 are shown in E1:E2. 0. 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. Jul 23, 2014 #1 I am trying to find the average for multiple columns using a criteria from another column. 1. Your usage may be, =AVERAGEIFS (H:H, C:C, C2, G:G, "Buy") Share. Steps: 1. NOTE: AVERAGEIF is only available in Excel 2007 and later. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. Where Column B contains the numbers being averaged, and column A is your zones. I'd like. 1 Answer. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. My suggestion is:-. column_references: The columns which you want to average. About. Excel Sum Based on Multiple Column and Row Criteria. Cross posted multiple sites. However, the result needs to ignore any zeros that appear in the data. 2. Here's the formula I'll use: =COUNTIF (C2:C17,">100") This is a simple, two part formula: simply point Excel to the list of data to count, and the rule to count. You require an OR condition since any one row could not be both 1 and 2. AverageIFs multiple columns when meets multiple criteria in one column. 2. In this short tutorial video, you'll learn how to find the average sales from a data set, based upon two different criteria. I'd just have to order those fields to get the result I want. To use AVERAGEIF, you simply need to click on the cell where you want the function, and type the syntax of the AVERAGEIF function. The AVERAGEIFS function will take these criteria and ignore the entire row if there is a blank. Type "=AVERAGEIF (" into a blank cell. Thus, 5 columns are reserved for the category of a given record. That will get you the following table. I have 2 Tabs, Tab 1 = Multiple (2) Tab 2 = Auto Created --> lets call it "Sheet1" Sheet1: Column A - is Date (15 min interval) Column B - Lets call it "CPU" Column C -. AVERAGEIFS with multiple criteria on one range Originally Posted. In this case, the AVERAGEIFS function comes as a savior. Excel: how to. 9. – Gandalf96. 0. criteria_column1 – The. 1. While the original AVERAGEIF function was designed to let you apply a criterion to the range of numbers you want to sum, much of the time you’ll need to apply one or more criteria to other columns. The AVERAGEIFS function syntax has the following arguments: Average_range Required. 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). To achieve what you describe, you can use this array formula, which must be confirmed with Ctrl - Shift - Enter. To get an average of data in a column with multiple criteria the following formulae may be used. Data / Get & Transform / From Table/Range. Hi All! This is a slightly tricky one. =AVERAGE (B3:B9) returns the average 4. It can take arguments where the first argument is the range column in which the average needs to. 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'. Rather than spanning dates across the columns on a single row, you should use a single date column. The article below explains two approaches. Finally, find out the average of the given range of cells. Clear searchLooking for a formula that averages the numbers located in the 'Salary' column as long as any of the priority columns in a row contain both 'Car' and 'House' in any order, so that this. 2 Apply AVERAGEIFS Function for Multiple Columns. Criteria Required. Arguments can include numbers, cell references, ranges, arrays, and. ) 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. Excel: AverageIf Multiple Criteria, sometimes one criteria will be empty. Note that the desired values that we wish to average are present, along with a bunch of #DIV/0! errors. The formula I made was the following:In column C, there are 3 formulas, to verify the result of the AVERAGE formula. 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. While I need 1 conditions_range that is only 1-column-width and 1 average_range that is 5-columns-width. 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. I will show you how to average specific numbers in. When say conditional average, we think of Averageif, but in this sc. Multiple columns. The formula is in the "KPI" tab (cells B2:B5) I am looking for the average of column T in the data tab, if, Column Q data = Column A KPI and if the dates in column D on data are. It is the first range that is evaluated. Criteria_range1 is required, subsequent criteria_ranges are optional. AverageIFs multiple columns when meets multiple criteria in one column. 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. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. Excel begin AverageIF function. Nov 7, 2021 at 0:27. Averaging a range. 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'. Columns G & I have text values and Column K is a number value. . 1. It also supports the specification of multiple columns to compute. WorksheetFunction. Today I will show you a quick and easy way to take the average of a group of data with multiple criteria. If done correctly then excel will put {} around the formula. Re: averageif across worksheets. which is the call durations column in the table: =AVERAGEIFS(data. Using AVERAGEIFS Function. g. I am trying to sum a total with multiple criteria (a. g. Suppose you have a table listing the scores of two tests. Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor. 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. AVERAGEIF with multiple criteria. Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. In this case, we're checking cells C2 to C17, for all values greater than $100. criteria_range1 is required. First, create a column to count how often a record appears on the data. ) – Mrig. For a single condition, we used AVERAGEIF, but for multiple conditions, we used AVERAGEIFS. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. 1 Answer. I need to find get the percentage of the last column the "MS DRG CMS Geometric Mean LOS" monthly. g. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. 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. Type =AVERAGEIFS. 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. =AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"}) There are more ranges, but the coding for those is fine. I have no problem running this on a single column of data but get lost when trying to apply the average to multiple columns. find average for multiple values in rows and columns in Excel. 1. Here is a screenshot to clarify. Criteria_range1, criteria_range2,. You require an OR condition since any one row could not be both 1 and 2. I have 10 years worth of 24-hour data in range G3:P35162 and need to average only the data that meets the criteria of being equal to "Jan" in range A3:P35162 and "1" in range D3:D35162. 0. 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”. It works fine if I only have one of the D column criteria (either one of them), but not with both. The input in columm A is time. 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). Type "," and then enter the criteria you want to use (e. )] I hope I was able to explain what I want. WorksheetFunction. 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. Thread starter mgirvin; Start date Dec 6, 2011; M. In. In the above, the sum of the blue/greens is 23, their count is 5 making the average 4. Type "," and then enter the criteria you want to use (e. My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach. 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. One is Name Box and another is Formula Box. It would be best if you store your variables in Cells. From the selected column, it will search the lookup value to calculate the average of these values. Below is one more solution for the sameI want to average data in those columns if specific multiple criteria is met in other columns. Row 2: 104 – 99 = 5. Then we create a COUNTIF function to count the number of records that appear more than once: =count. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. A not so average solution to an uncommon problem in Excel. Type "=AVERAGEIF (" into a blank cell. If you apply this method on a series object, it returns a scalar value, which is the mean value of all the observations in the pandas DataFrame. Otherwise it increases the value of the previous cell by 1. Is there a way to do. Follow. Windows; Dec 6, 2011. Using SUMPRODUCT Along Multiple Columns and Rows. The AVERAGEIFS function works like AVERAGEIF, except it is designed to apply multiple criteria. Name Prior. Yep! Averageif is capable of handling multiple text conditions in one column. COUNTIFS: Excel 2007+: Counts the number of cells within a range that meet multiple criteria: IF: Specifies a logical test to perform: OR If 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. Would have used AverageIfs in excel for this calculation. This formula will first calculate the average of all values in column A greater than 50 using AVERAGEIF, and then round the result to two decimal places using the ROUND. Because we already have unique departments in column F, we can reference the list directly. 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 for multiple criteria. the formula that builds the average column I want to be similar to how excel's AVERAGEIFS folrmula works. The total amount for the groceries is $78. 365; Platform. 2. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. Let’s use the procedure discussed in the following section. MATCH (A2,F1:H1,0) Return the data in the column. RangeForCriteria1: C2 to C8. How do i get an average of a column, with multiple conditions herof an OR condition. We take a dataset that includes some students,. Averageif multiple columns. Average for multiple criteria in. Formula. In this instance I want to exclude the zero value from the calculation. Sample usage. Administrator testing addon Join Date 12-29-2011 Location Duncansville, PA USA MS-Off Ver Excel 2000/3/7/10/13/16/365 Posts 52,694 STEPS: Firstly, go to the tab on the ribbon. You may want to make sure that the range size and shape of each column reference is the same. Then your calculations table would be quite. Are you allowed to add a column to your data for calculating the total_price? For example, column E = Quantity * Price. It can also be solved. Result. criteria1 is required. But we can’t get the average with the AVERAGEIF function when it comes to multiple criteria. The Convert Text to Columns Wizard –. 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. Multiple AVERAGEIF from a List Hi all, I'm trying to average specific values from a list I have. Coachcasa New Member. Calculate average of numbers in a column using column number. criterion is the condition to be tested. Type the formula ‘=VSTACK (‘ to start the VSTACK function in Excel. 6. 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. In Excel 2016+ (may be different in earlier versions): select some cell within the data table. . Use SUMPRODUCT:AVERAGEIFS returns the average of all cells that meet multiple criteria. e. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. 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). Averageifs with Or. Average multiple values from multiple columns in Excel. The take-away is: AVERAGEIFS returns the correct value in D2, despite the #N/A errors in the ranges. Let us see how to apply this function with multiple ranges. Now, you can see that in the AVERAGEIFS function, cell range D5:F13 is selected as average_range. The AVERAGEIF function is one of the older functions used in spreadsheets. I want to average column K if certain criteria are met in Columns G & I. Re: average multiple rows and columns that meet a certain criteria. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. After this, you. Syntax AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF. Range("M:M"), _ Sheets(modelName). I'd like to calculate the average duration per TestName (column A) between the 2 timestamps (B and C) in another sheet. 2. In Column N2 and NO I put those 2 times respectively. In this video show you how to calculate the average values across multiple columns with a filterin. 0. Specifying Multiple Range. Criteria_range1, criteria_range2,. =count. 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. You are looking for the AVERAGEIF function seen here. AverageIf(Range("F5:F30"), "Savings",. AVERAGEIFS / SUMIF with 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. SUM will also get the average of G (where E="A") for every row of F=0 if the same. 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 calculation for multiple non adjacent columns with criteria. 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). This guide will demonstrate how to calculate the average with multiple criteria in Excel. Failed attempts. 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. AVERAGEIFS needs all ranges to be the same size (including the range to average) so if you want to average multiple columns based on multiple criteria in single columns then it won't be possible to use AVERAGEIFS - try using an "array formula" like this: The issue arises when there are multiple columns with same group as Averageif takes the first column only. Criteria is the condition to apply, along with any logical operators that are needed. 2. To exclude can use “<>#N/A. 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. . Count the number of transactions $100+ with this Excel COUNTIF formula. Example 1: AVERAGEIFS with One Character Column. The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not blank. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. The AVERAGEIFS function solves our. This function can be used to know the average balance in the inactive bank accounts with an updated KYC. Even using the 1 average column, I'd need an averageif to average the supervisor's staff's averages, unless I just want to hard-code it onto Sheet1, but I'd. 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,. criteria_column1 – The. 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. SUMIFs and AVERAGEIFs for LARGE N with multiple criteria & range Hi, quick and simple question I have a data set with products, prices, product categories, revenues and quantities sold. Description Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. I would also avoid using whole column references where possible. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub. =SUMIFS (C2:C9, C2:C9,">=200",. criteria_column1 - The data. Define a range name to non-contiguous ranges. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. That’s my simple answer to the above question. 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. =SUM (ROW (A1:A4)) and press CTRL+SHIFT+ENTER from within the formula bar to convert this to an array formula. I think the best way to do this is to just calculate the average the old fashioned way:. Note that, we have changed the column 1 title from Month to Month Number so as to include the numbers inside the formula. See Efficient Spreadsheet Design. The AVERAGEIFS function in Excel can calculate the average of cells only with values using multiple criteria. 2. 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. Edit: In the mean time, I have created two helper columns, that are just the Month() and Year() of each row of data. 1. See moreThis article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. Assuming I know the correct column, the formula is easy: AVERAGEIFS(COLUMN B, DATE,>= START DATE, DATE, <= END DATE) What I'm struggling with is how do I identify the correct column using the location names?Step 1: Click on an empty cell where we want the average to be calculated. Perhaps try something like this: Code: Sub example () Dim av As Double av = Application. To calculate a conditional average for multiple columns of data, you can use the AVERAGE function with the FILTER function.