This sort of ambiguous situation it can't compute. George has a worksheet that includes dates (in column A) and values associated with those dates (in column B). Assuming you have a list of items in column A, planned sale figures in column B, and actual sales in column C. 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. 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. Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. Paste the M-Code below into the window that opens. UPDATE2: I'm including another screenshot. Example 1: AVERAGEIF Function in VBA. Sub AverageIf() Range("F31") = WorksheetFunction. Returns the average of a data column depending on multiple criteria. 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. average_range is required. Criteria Required. Criteria_range1 is required, subsequent criteria_ranges are optional. )] I hope I was able to explain what I want. Therefore the cells in the range are used to calculate the average. Similarly, the function can. Here is a screenshot to clarify. When say conditional average, we think of Averageif, but in this sc. 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. . 0. I am wanting to do an averageifs statement, but it does not seem that is supported. Criteria_range1, criteria_range2,. Which can take in multiple criteria to determine what to average. Make sure to start the formula with Equals Sign. Array formula 1: find value with two or multiple criteria in Excel. I will show you how to average specific numbers in. 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). =AVERAGEIFS(B:B,A:A,"=Apple",A:A,"=Pear",C:C,"=Yes"). kckliodna; Aug 25, 2023; Excel Questions; Replies 3 Views 452. criteria_column1 - The data. 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. AVERAGEIF function on different cells in different sheets. Thus, 5 columns are reserved for the category of a given record. AVERAGEIFS function in Excel is used to find the average from the target range of cells when more than one condition is met. Excel: how to. After some experimenting, I have found that the following formula successfully reports the correct answer: {=AVERAGE (IF ( list_of_names = local_name ,IF ( sector_percent >0,IF ( sector_count > min_number_sectors, sector_percent ,0))))} If you strip the list_of_names and local_name variables from the AVERAGEIFS formula, it. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. Excel can calculate a sum, average or count of values based on specific criteria by using the SUMIFS, COUNTIFS and AVERAGEIFS functions. 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. . You can use the AVERAGEIFS() formula to achieve what you want. If done correctly then excel will put {} around the formula. AVERAGEIF (or AVERAGEIFS) is not the function you want. However, the result needs to ignore any zeros that appear in the data. Select the columns or rows you want to compare. Since your. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. 2. 1 Using AVERAGE and AVERAGEIF Functions. 1 Answer. My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach. 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. The generic syntax for AVERAGEIF looks like this: = AVERAGEIF ( range, criteria, average_range) The AVERAGEIF function takes three arguments: range, criteria, and average_range . The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. It is my understanding an averageifs will not work because it will not do multiple criteria in the same column. Using the AVERAGEIF function is quite similar to using the SUMIF functions in the cells. And you always get a #DIV/0 when you try and average nothing. Example 1: AVERAGEIFS with One Character Column. AverageIFs multiple columns when meets multiple criteria in one column. Criteria_range1, criteria_range2,. The take-away is: AVERAGEIFS returns the correct value in D2, despite the #N/A errors in the ranges. I am using the averageifs function, and have one column where I need to calculate the average if either of the criteria are true. COUNTIFS: Excel 2007+: Counts the number of cells within a range that meet multiple criteria: IF: Specifies a logical test to perform: ORIf you want to include all the types you simply omit this condition, if you want to include selected types you can use array constant as shown above. AverageIFs multiple columns when meets multiple criteria in one column. The first parameter is the , which is A2:A10. AVERAGEIFS Multiple Tables. I need the formula to find the value of "BF3" in every sheet from A3 down to A100 but i need the average value. To begin this method, double-click on cell D10 and insert the formula below. 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. 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. ), value_if_true, value_if_false) Translated into a human language, the formula says: If condition 1 is true AND condition 2 is true, return value_if_true; else return value_if_false. 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. WorksheetFunction. The result returns 7 (average value). See screenshot: 2. So for example, say I want the average between 09:00:00 and 09:30:01. AVERAGEIF function using reference as criteria range. He would like to calculate the average of all the values for a given month in a given year. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Countif with average. =AVERAGE (column_references) ARGUMENTS. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. 1. Using the simple set of values in Figure A, the AVERAGE () function in D3. For the second part you do the same, but switch the AVERAGE. 1. 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. The criterias would be by year (have multiple years), then by Pt type (in or out Patient),. The formula can use any worksheet function and use any fields from the data source. You can use the AVERAGEIFS function to a single column to determine the average. 2. The AVERAGEIF formula within the Google Sheets application. In G1 get the average value for in column C for all seconds matching what is in F1. 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 this example, we will demonstrate the use of the AVERAGEIFS function based on multiple criteria. The AVERAGEIFS function will take these criteria and ignore the entire row if there is a blank. criteria1 is required. I have a tab with a table. 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. For example, it calculates: Row 1: 100 – 90 = 10. If you do not want to add multiple range references to the AVERAGE function, you can give a range name to the. 0. As Jeff pointed out, the function will silently resize your ranges to suit that restriction - so for example if you typed:. Range("A1")) The problem i'm running into is that the last criteria range does not run through the full column of data in. Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor. Alternatively, you can also use the “Formula bar” box located on the top of columns after selecting the cell; and insert. AVERAGEIFS() requires that all the ranges must be the same size and shape. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. 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. e. Excel begin AverageIF function. 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. 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). Read below for details. Today I will show you a quick and easy way to take the average of a group of data with multiple criteria. 2. 0. Syntax. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. New column contains the formula IF (OR (AND (ColA >= Date1, ColB <=Date2), AND (ColA >= Date3, ColB <= Date4)),TargetCol,"") This will return the. Select Done to complete the function. e. This can be accomplished by using multiple AVERAGEIFS function to the formula. I have attached a sample of the sheets I am using. A good way to do this is to use the AVERAGEIFS function, which can calculate averages based on one or more criteria. 2. I'm trying to make use of Averageifs with multiple criterias in multiple columns. I have manually averaged some of the formulas to double check them and they are not computing accurately. From the list of products, I took the unique values for a certain month. 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. I have about 8 other columns of Data to be included What i need is a formula that. AVERAGEIFS with multiple criteria on one range Originally Posted. You are looking for the AVERAGEIF function seen here. Joined Feb 1, 2021. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. 2. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. 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. Hi All, I am trying to calculate average based on multiple column. Like SUMIFS, AVERAGEIFS will allow us to average rows or columns in a table that satisfy multiple specified conditions. 1. It is because A and B are merged columns in this area. A | B 1 | Name 1 | Value 1 2 | Name 2 | Value 2 3 | Name 2 | Value 3 4 | Name 3 | Value 4. Syntax is AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3,. Windows; Dec 6, 2011. 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). 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. 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. Combining the averageifs function I think does not work with "or" criteria, so I tried this [but it's not working]. Finally, find out the average of the given range of cells. 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. Please help with a formula. 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. 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. One or more cells to average, including numbers or names, arrays, or references that contain numbers. AVERAGEA: Returns the numerical average value in a data set. =AVERAGEIF (B:B,F1,C:C)Hi Creative999, AVERAGEIFS doesn't have an OR option. For older versions, you'll need to use a third column, C. The formulas in D1:D2 are shown in E1:E2. 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. 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 found the following post: AVERAGEIFS with multiple criteria on one range In which T. The formula will only show the value listed in the first month and will not average across the months. 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. The first formula is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. How to use AVERAGEIF in Google Sheets, only addressing columns with a specific text. Averageifs with Or. . But as demonstrated below, they do not. What I'm trying to do is find the average for each category, excluding the 0s. Two-column example. As the formula is copied down, it calculates an. 08-12-2022 12:38 AM. Follow. 2. AVERAGE using multiple criteria from. criteria_column1 - The data. But the columns above are the only ones that will need to be in the 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. Average for multiple criteria in one column. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. Below is one more solution for the sameI want to average data in those columns if specific multiple criteria is met in other columns. 2. Finally, we'll take a look at Excel's AVERAGEIFS function. In such cases, you can use the AVERAGEIF and AVERAGEIFS functions. Excel Finding Average value based. 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. I cannot perform this function using AVERAGEIFS since the ranges are not the same size, spanning multiple columns. I'm trying to calculate a percentage value which uses an AVERAGEIFS function. Cross posted multiple sites. For example, we can compute the average of the amount column, but only include those rows where the customer column is equal. Thread starter mgirvin; Start date Dec 6, 2011; M. Here is an example that you can use. up to 255 total. For example, we are given the total cost of preparation of three items. This help content & information General Help Center experience. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub Returns the average of a data column depending on multiple criteria. Result. So how would I be able to take out the average of a table using dynamic columns and rows. By AndySan in forum Excel Formulas & Functions Replies: 3 Last Post: 12-05-2013, 05:35 PMNow wrap this in SUM e. Using SUMPRODUCT Along Multiple Columns and Rows. 3. Blank and text values are ignored. 67. The above formula. criteria_range is the set of categories containing the specific criterion whose average will be taken. 0. g. From the remarks for the AVERAGEIFS function: Each cell in average_range is used in the average calculation only if all of the corresponding criteria specified are true for that cell. You can apply the AVERAGEIFS function on a single column to calculate the average. 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 in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. 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. Let us see how to apply this function with multiple ranges. I'd just have to order those fields to get the result I want. 1. 1 to 127 ranges in which to evaluate the. Starting in Excel 2007, you should always use the SUMIFS, COUNTIFS, and AVERAGEIFS functions, and in Excel 2016 MAXIFS and MINIFS functions, instead of SUMPRODUCT. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. Note that the desired values that we wish to average are present, along with a bunch of #DIV/0! errors. I'm guessing this is probably why I'm getting errors. Average for multiple criteria in one column. Example 2: Using AVERAGEIFS for Multiple Columns. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . MATCH (A2,F1:H1,0) Return the data in the column. 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). Multiple columns. You require an OR condition since any one row could not be both 1 and 2. I have tried =Average (IF ( (A:A="Apps")* (B:B="4")* (C:C="new")+ (C:C="open. 0. Sub Averageif_Function() Range(" E2") = WorksheetFunction. 2. 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. 2. You could spare a row and place the array answer in multiple cells with CTRL+SHIFT+ENTER as INDEX((YEAR(A1:Z1)=A2)*(A10:Z10),1,0) - then operate the AVERAGEIF on the range. AVERAGE using multiple criteria from multiple columns. 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, you could re-create AVERAGEIF quite easily: So the formula in G2 to re-create AVERAGEIF could be: =AVERAGE (IF (A2:A5=G1,D2:D5-B2:B5)) Note: This formula is an array formula and needs to be confirmed through Ctrl Shift Enter. The syntax for AVERAGEIF is: =AVERAGEIF (Range,Criteria,Average_range). The VSTACK function accepts 1 or. stack the 2 ids and 2 weights columns to create dataframe with 1 ids and 1 weight column. find average for multiple values in rows and columns in Excel. Use the following formula for G1 and copy down to the bottom of your list in column G. . For example, you may want to find the average of a column only for specific regions or months. Averageif multiple columns. 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. This guide will demonstrate how to calculate the average with multiple criteria in Excel. Select the range of cells you want to evaluate. This would take your multiple columns and make them rows. Is there a way to do this which doesn't involve IF(AND(, can AVERAGEIF or IFS be used or something else to keep it simple. columns(2). Share. 9. This way, other members can see what has already been done in regards to a. The difference between Averageif and Averageifs in Google Sheets is in the criteria used. Under each month I have a reported lab value. Then select the merged cells. 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. Id like to build a new column in my PowerBI table called "average". 5. About;. Description Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. . I have a spreadsheet with a col of phases (Phase 1, Phase 2, etc. =AVERAGE (IF ( (C2:C5)* ( (D2:D5="US")+ (D2:D5="Germany")),B2:B5)) After array-entering the formula, the formula bar will show. criterion: The pattern or test to apply to `criteria_column`. For a single condition, we used AVERAGEIF, but for multiple conditions, we used AVERAGEIFS. There is a formula that does this for you called AVERAGEIFS. I've tried =AVERAGE(IF(D2:N28=P16,E2:N28)) but it only seems to average the first column in the range of columns that contain numbers to average. You can add as many criteria_range,criteria in AVERAGEIFS. 0. Columns(20), _. This article explains how to. g. 0. The AVERAGEIF syntax is a bit different: =AVERAGEIF (range, criteria, [average_range]) Where 'range' is the range containing your criteria, and. Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) Hot Network Questions Will recording as MIDI lose the quality of a WAV? Who am I? Mind, body, mind and body or something else?C3:M17 entire target 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. Stack Overflow. I discovered the QUERY function and 'Google Visualization API Query Language'. Now, Excel SUMIFS cannot look up matching values for horizontal criteria. 1. Unsure where my errors are on this one. The Convert Text to Columns Wizard –. Go through the steps below to accomplish this. so on so forth. I used the AVERAGEIFS command but I recieve a message that "excel the formula you typed contains an error". I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. 0. 1. Aug 2, 2017 at 10:41. Type "," and then enter the criteria you want to use (e. The AVERAGEIFS function is a built-in function in Excel that is categorized as a Statistical Function. 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. Criteria_range1 is required, subsequent criteria_ranges are optional. RangeForCriteria1: C2 to C8. In this video show you how to calculate the average values across multiple columns with a filterin. Name Prior. We can add as many criteria as needed to determine our final results. 0. 6. Averageifs for multiple criteria. 2. I am trying to find the average of column G based on the three largest numbers in column B. "<1000") Select the range of cells you want to average. how to use multiple criteria in AVERAGEIF function. Example 1: Average If Not Blank (One Column)Example AVERAGEIFS function, step by step: Select the cell H3. 365; Platform. 0 How to use Averageifs to Calculate average with multiple criteria and ignore text. Add another pair of parameters, one which gives the range of the names and another which gives the. 33, or how many Man in China which should average 2. = 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). STEPS: Firstly, go to the tab on the ribbon. The Regexmatch will help us to use multiple text conditions in one column in the Averageif function in Google Sheets. The AVERAGEIFS function syntax has the following arguments: Average_range Required. After this, you. Average ( Table. There are two fields in the Insert Calculated Field dialog box. conlan New Member. 1 Answer. criterion is the condition to be tested. What are multiple condition averages? They are averages based on a column of numbers that include rows that meet one or more criteria. So for example in the sample above, how can I take out the average of "A" and "B" and "Z" and "L", such that I can change A to B and the answer changes. Double click the AVERAGEIFS command. The formula: =1/ (critRng1=TRANSPOSE (crit1))*1/ (critRng2>=DATE1)*1/ (critRng2<=DATE2)*avgRng. 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)))). 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. The issue arises when there are multiple columns with same group as Averageif takes the first column only. So if I need, say, a. Each cell in column C is multiplied by its corresponding cell in the same row in column D, and the results are added up. Subsequently, insert the following. It is the first range that is evaluated. 0. 2. See moreThis article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. 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,. 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). Cell C3 sums all the numbers in range A1:A5:. Data / Get & Transform / From Table/Range. 1. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. Most solutions show how to use it with AVERAGEIF() and not AVERAGEIFS(), which has a different structure. Average. The AVERAGEIFS uses the following syntax: average_range, criteria_range1, criteria1, criteria_range2, criteria2, etc. YES, that completely helped. AVERAGEIFS uses all criteria you offer to select a number. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. SYNTAX. reaction [Total Won] >0. Steps: 1. , row 2) with the Month of the collection date in row 1, and referring to that column in an AVERAGEIFS. 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. The excel AVERAGEIFS function is used to find the average of values in excel where more than one (multiple) conditions satisfy. AverageIf(Range("F5:F30"), "Savings",. As the formula is copied down, it returns total hours in Sheet1, Sheet2, and Sheet3 for the projects shown in column E. 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. To average numbers based on multiple criteria, you can use the AVERAGEIFS function. I. Syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],. Sorted by: 1. The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. 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. =AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2], [criteria2],. 2. The formula I made was the following:In column C, there are 3 formulas, to verify the result of the AVERAGE formula.