As the formula is copied down, it returns total hours in Sheet1, Sheet2, and Sheet3 for the projects shown in column E. 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. The average function evaluates the zero but ignores the blank cell and the. criteria_column1 - The data. No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function. Using a Table (or excluding the header row), avoids that difficulty. I have about 8 other columns of Data to be included What i need is a formula that. To create a column average for a specific category, I would need to include all five of those columns in the criteria range. . conlan New Member. g. Steps: 1. But when it comes to comparing two or more arrays, especially with multiple criteria, SUMPRODUCT is the most effective, if not the only, solution. I feel like I am missing something simple or easy that is not locking in the specific data I would like to compute. This function differs from AVERAGEIF in that it allows you to specify multiple criteria that indicate which cells to average. Criteria_range1 is required, subsequent criteria_ranges are optional. This article explains how to. 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). Full formula should look like:It will also do that if there previous cell is equal to "". Average with multiple conditions. 0 How to use Averageifs to Calculate average with multiple criteria and ignore text. I have a range, where I want to average column values in column A for rows that have column value B equal to 2 and column A values lesser than 3, so is this correct: WorksheetFunction. Enter the following formula in S2: =--(R2>0)Average for Multiple Criteria. Hope that helps. [SOLVED] AVERAGEIF Multiple Columns Multiple occurrences. 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. I've tried to do an averageif formula and all sorts but it won't work with the categories. 2 Apply AVERAGEIFS Function for Multiple Columns. The result returns 7 (average value). You can use AVERAGEIFS formula for multiple conditions. Select the range you will average all vlookup findings, and click Kutools > Content > Advanced Combine Rows. I want to accomplish what this formula does, but to include. To perform a dynamic two-way average with a formula, you can use an Excel Table, the UNIQUE function, and the AVERAGEIFS function connected to the spill ranges returned by UNIQUE. kckliodna; Aug 25, 2023; Excel Questions; Replies 3 Views 452. 2. Finally, we'll take a look at Excel's AVERAGEIFS function. To create, select a cell, go to the Formulas tab, and select More Functions > Statistical > AVERAGEIF. You can use the AVERAGEIF function to calculate the average lookup value. 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. I would also avoid using whole column references where possible. 0. . 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. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. I'm guessing this is probably why I'm getting errors. Excel begin AverageIF function. I discovered the QUERY function and 'Google Visualization API Query Language'. 2. Thanks for the quick reply. conlan New Member. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. 2. When the criterion is a number value, simply enter the. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . In the UI, open the Advanced Editor. I'd like. OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i. 1. We take a dataset that includes some students,. I think the best way to do this is to just calculate the average the old fashioned way:. ) – Mrig. Below is one more solution for the sameI want to average data in those columns if specific multiple criteria is met in other columns. And the. A not so average solution to an uncommon problem in Excel. The FREQUENCY function performs a series of "count if" operations based on the arguments provided. Just keep in mind that parameter orders are slightly different from AVERAGEIF. It can take arguments where the first argument is the range column in which the average needs to. This would take your multiple columns and make them rows. The following examples show how to use each formula in practice. 1 Using AVERAGE and AVERAGEIF Functions. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. This is the syntax of the AVERAGEIFS function. To get an average of data in a column with multiple criteria the following formulae may be used. I used the AVERAGEIFS command but I recieve a message that "excel the formula you typed contains an error". We can use AVERAGEIFS to solve this problem by excluding. Example 1: AVERAGEIFS with One Character Column. Using =averageifs with multiple text criteria in different columns. 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). 2. 3. So I would like to select the 2011 year, the month of May and get the average of the values in column C that satisfy these requirements. 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. In Excel, we often need to calculate the average of multiple columns based on multiple conditions. I'm trying to calculate a percentage value which uses an AVERAGEIFS function. g. g. This code works when considering only one metric: +QUERY(my_table," SELECT Col1, AVG(Col4). One or more cells to average, including numbers or names, arrays, or references that contain numbers. 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. 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. criteria1 is required. One or more cells to average, including numbers or names, arrays, or references that contain numbers. Use SUMPRODUCT:AVERAGEIFS returns the average of all cells that meet multiple criteria. Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor. Joined Jul 23, 2014 Messages 2. Specify the range to check against the 1st condition (B3:B15 - items). Result. , row 2) with the Month of the collection date in row 1, and referring to that column in an AVERAGEIFS. Average for multiple criteria in one column. To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). Averageifs with Or. Here's an. You require an OR condition since any one row could not be both 1 and 2. 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. Does that make sense?Range("C1:C676") = Application. The worksheet includes values for the last several years. The AVERAGEIF syntax is a bit different: =AVERAGEIF (range, criteria, [average_range]) Where 'range' is the range containing your criteria, and. Using the simple set of values in Figure A, the AVERAGE () function in D3. 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. As you can see clearly above, in Averageif the criteria range comes first, but in Averageifs it. Many more great Excel tutorials linked below:sure to watch my other Excel tutorial vid. 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. Rather than spanning dates across the columns on a single row, you should use a single date column. Your sheet structure could be improved to make analysis much more efficient. 0. In this example, the two criter. This formula can be easily solved with the AVERAGEIF function or the AVERAGEIFS function . 33, or how many Man in China which should average 2. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. reaction [Total Won] >0. It can also be solved. 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. Now follow the instructions at the top of that screen. Row 3: 98 – 102= -4. Please help with a formula. But the columns above are the only ones that will need to be in the formula. The AVERAGEIF formula within the Google Sheets application. 5. 09-17-2012, 03:12 PM #5. I need to find get the percentage of the last column the "MS DRG CMS Geometric Mean LOS" monthly. For example, we are given the total cost of preparation of three items. 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". Now, you can see that in the AVERAGEIFS function, cell range D5:F13 is selected as average_range. AVERAGEIFS returns the average of all cells that meet multiple criteria. Using Averageif for 2 criteria in an Array in. 2. I currently use a SUBTOTAL function to either sum, count or average a bunch of cells in a range. A couple of ways to overcome this. You can add as many criteria_range,criteria in AVERAGEIFS. Then your calculations table would be quite. The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. The AVERAGEIFS function syntax has the following arguments: Average_range Required. I am wanting to do an averageifs statement, but it does not seem that is supported. 0. 67. Select the columns or rows you want to compare. The AVERAGE function calculates the average of numbers provided as arguments. I basically need to get the average of the values in the J column of all rows left after those three criteria are met. AVERAGEIFS() requires that all the ranges must be the same size and shape. 2. YES, that completely helped. In my small example below with 3. Thread starter Coachcasa; Start date Feb 1, 2021; C. Yep! Averageif is capable of handling multiple text conditions in one column. This help content & information General Help Center experience. The AVERAGEIFS function works like AVERAGEIF, except it is designed to apply multiple criteria. =AVERAGEIF (Item:Item, "Shirt", [Quantity Ordered]: [Quantity Ordered]) Description. So, I'd want the average for fruit, (so the orange, apple and banana columns), but only if the value is not 0. It returns a number that represents the average (arithmetic mean) of the arguments. Average multiple values from multiple columns in Excel. This can be accomplished by using multiple AVERAGEIFS function to the formula. averageifs w/ multiple average ranges in different columns. – Gandalf96. Viewed 505 times. To start off, let's calculate a straight average with the AVERAGE function for reference. The formula can use any worksheet function and use any fields from the data source. average_range is required. 1. AVERAGEIF (criteria_range, criterion, [average_range]) Let’s look at the first case. Criteria is the condition to apply, along with any logical operators that are needed. . AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. =AVERAGEIFS(N1:N612,I1:I612,{"FL","IF"}) There are more ranges, but the coding for those is fine. Let’s use the procedure discussed in the following section. Hi All! This is a slightly tricky one. I want to know the average of all Cat that are in the UK only, which would be 3. Neither is working. Yields the answer 6, so AVERAGEIFS (or an alternative formulation) should be able to produce an average for those 6 cells. I took it for granted that #N/A errors in one of the ranges would cause a problem. By thatguytg53 in forum Excel Formulas & Functions Replies: 11 Last Post: 03-11-2014, 06:17 PM. find average for multiple values in rows and columns in Excel. Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. It ignores the “Sick” value in cell B6 since the AVERAGEIF function does not assign numeric values to text. You have multiple arguments. criteria_column1 – The. In Excel 2016+ (may be different in earlier versions): select some cell within the data table. Here, I selected cell H4. "<1000") Select the range of cells you want to average. Description. 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. e. Using SUMPRODUCT Along Multiple Columns and Rows. 0. Here is the same formula with an adjusted range to C2:N22. Use the following formula for G1 and copy down to the bottom of your list in column G. microsoft-excel; worksheet-function; Share. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. To include all October dates you should use either "<="&DATE (2023,10,31) or "<"&DATE (2023,11,1) Average with multiple criteria in same and different column. Formula. 1 Answer. This way, other members can see what has already been done in regards to a. You can use the following methods to write AVERAGEIF and AVERAGEIFS functions using VBA in Excel: Method 1: AVERAGEIF Function in VBA. The Microsoft Excel AVERAGEIFS function returns the average (arithmetic mean) of all numbers in a range of cells, based on multiple criteria. Any record appearing more than once (>1) is considered a duplicate. 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. Applying AVERAGE and FILTER Functions Applying AVERAGE and FILTER functions is one of the smartest ways to calculate. My suggestion is:-. Another difference is the positioning of arguments. Make sure to start the formula with Equals Sign. Averaging a range of values is easy. . Criteria Required. AVERAGEIF Function. 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 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. Description Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). Average for multiple criteria in one. But we can’t get the average with the AVERAGEIF function when it comes to multiple criteria. AVERAGEIF Function. Range("A1")) The problem i'm running into is that the last criteria range does not run through the full column of data in. Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) 0. I. To average numbers based on multiple criteria, 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. The mistake in the formula is that it only takes one argument for the range. Select Done to complete the function. Row 2: 104 – 99 = 5. So you just need to add one more criteria for BH US Agent Web form. To calculate an average when corresponding cells are not blank, you can use the AVERAGEIFS function. Then select the merged cells. 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. This is my formula =AVERAGEIFS (January:July!A3:A100,BF3,January:July!G3:G100) I have 7 seperate worksheets Jan-Jul which every sheet is identical in column setup. 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. 08-12-2022 12:38 AM. See moreThis article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. . AVERAGEIFS / SUMIF with multiple criteria. I have no problem running this on a single column of data but get lost when trying to apply the average to multiple columns. 0. 0. criteria_column1 - The data. Where Column B contains the numbers being averaged, and column A is your zones. I have used your code of calculating rolling average but. 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. 1. Here is a solution using a helper column: Select all five sheets by clicking on the sheet tab of Sheet1, then Shift-clicking on the sheet tab of Sheet5. Note the lookup_value in XLOOKUP is 1 since the logical expressions in lookup_array create an array of 1s and 0s. 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"). AVERAGEIF based on multiple columns in google spreadsheet. To do that you could calculate average by yourself. . e. My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach. Fill one cell depending on value of another. Search. The procedure to apply the Excel AVERAGEIF function with the required date criterion is: Select cell G6, enter the formula =AVERAGEIF(B2:B11,G3,C2:C11), and press Enter. Thread starter mgirvin; Start date Dec 6, 2011; M. Breaking my head over this, time to look for help :(I have a sheet with raw data, as illustrated below. 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. I am trying to find the average of column G based on the three largest numbers in column B. But as demonstrated below, they do not. 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. If done correctly then excel will put {} around the formula. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. Jul 23, 2014 #1 I am trying to find the average for multiple columns using a criteria from another column. Sample usage. I have created a simple formula using AVERAGEIFS - using multiple criteria, it selects rows that match the criteria and returns an average for the numbers which occur in the selected column for those matching rows. Using the AVERAGEIF function is quite similar to using the SUMIF functions in the cells. Name Prior. This cell range contains multiple columns. Using AVERAGE and IF Functions 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: =AVERAGEIFS(C3:C15, B3:B15, ">=9/1/2022", B3:B15, "<=10/30/2022") With cell references: =AVERAGEIFS(C3:C15, B3:B15, ">="&E3, B3:B15, "<="&F3) This article describes the formula syntax and usage of the AVERAGEIFS function in Microsoft Excel. In this instance, select B2 through B16, to identify the Age column as the range. As Long Dim TempRange As Range Dim TempArr As Variant Dim MyArray As Variant a = Application. Average for multiple criteria in. When using multiple criteria, you can apply them to the same range as we did with previous. 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. Still an array formula. In this section, we will use. It also supports the specification of multiple columns to compute. 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. Calculating Average by multiple columns. The excel AVERAGEIFS function is used to find the average of values in excel where more than one (multiple) conditions satisfy. I've found the following post: AVERAGEIFS with multiple criteria on one range In which T. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub Returns the average of a data column depending on multiple criteria. 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. For example, you may want to find the average of a column only for specific regions or months. Our next example is based on a situation when we have blanks in multiple columns. As the formula is copied down, it calculates an. criteria1 is required. 1. D4:G4), rather than the four columns necessary to average the four median rents for 1991. Sub Averageif_Function() Range(" E2") = WorksheetFunction. Can seemed to get it right other than a formula that reads: (averageif red + average if yellow) / (counta (if yellow) + counta (if red)). AverageIFs multiple columns when meets multiple criteria in one column. Thus, 5 columns are reserved for the category of a given record. . The AVERAGEIFS function in Excel can calculate the average of cells only with values using 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. For example, in column A I have the year, in column B I have the month, and in column C I have the values. 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:. How to use AVERAGEIF in Google Sheets, only addressing columns with a specific text. Hot Network QuestionsIn this table i would be looking to get the average of all the "A" in column B that also have "D" and "E" in column C (Using the values in column F to get the average) however in some instances a combination (e. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. 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. 1. It allows you to average data in one range of cells where the data in another range matches a certain criteria. Select the range of cells you want to evaluate. This sort of ambiguous situation it can't compute. Coachcasa New Member. To use AVERAGEIF, you simply need to click on the cell where you want the function, and type the syntax of the AVERAGEIF function. Excel: averageif function for more than one cell. . While I need 1 conditions_range that is only 1-column-width and 1 average_range that is 5-columns-width. Otherwise it increases the value of the previous cell by 1. RangeForCriteria1: C2 to C8. A not so average solution to an uncommon problem in Excel. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column - The data column to average. 3. The result is given in the same datatype as the values in the list. 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. There are two fields in the Insert Calculated Field dialog box. 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. Specifying Multiple Range. 1. i have tried multiple ways, including this one: Avg = List. g. That’s where the AVERAGEIF function comes into play. 0. Then select the merged cells. Rather than using AverageIf (), try using Average (If ()) as an array formula for this kind of scenario. FORMULA. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). To exclude can use “<>#N/A. 365; Platform. To define criteria, AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be. However, the result needs to ignore any zeros that appear in the data. This cell range contains multiple columns. I can create the AVERAGEIF on each page to get the price average for each product types: =AVERAGEIF (range of product types;"name of the product type. If not included, `criteria_column` is used for the average instead. For convenience all data is in an Excel Table named data in the range B5:. 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. Follow. Nov 7, 2021 at 0:27. Select the range of cells you want to evaluate. Method-2: Finding Average If Cell Contains Text (Multiple Text Criteria) 2. This function can be used to know the average balance in the inactive bank accounts with an updated KYC. STEPS: Firstly, go to the tab on the ribbon. Today I will show you a quick and easy way to take the average of a group of data with multiple criteria. In this example, the goal is to calculate an average of the quiz scores in columns C, D, E, and F for each person. 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. Then you can merge this new table into your original one matching on Group, then expand the Average column. INDEX (F2:H5,0,MATCH (A2,F1:H1,0)) Calculate the Average of Result based on the returned column. 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. AVERAGEIFS: Returns the average of a range depending on multiple criteria. 3. Average. When say conditional average, we think of Averageif, but in this sc. AverageIFs multiple columns when meets multiple criteria in one column. AverageIFs multiple columns when meets multiple criteria in one column. I'm trying to make use of Averageifs with multiple criterias in multiple columns. This screenshot shows my attempt to replicate your setup: The formula in column D is based on the following steps: Find the column number corresponding to the category. For example: Player A scored an average of 6. 5. (3) Divide Sum by Count to get Average. Let’s assume you have the below table where we have the number of items in the stock and their selling price. AVERAGEIF: Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria: AVERAGEIFS Excel 2007+: Returns the average (arithmetic mean) of all cells that meet multiple criteria. Criteria Required. 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. How do I `SUM` by multiple columns in Excel. criteria_range1 is required. The AVERAGE, AVERAGEIF, and AVERAGEIFS functions all automatically ignore blank cells and any cells that contain text. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. 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 AVERAGEIF function in Google Sheets has the following syntax: =AVERAGEIF(criteria_range, criterion,[average_range]) Let us break down its arguments.