I cannot perform this function using AVERAGEIFS since the ranges are not the same size, spanning multiple columns. For example, we can compute the average of the amount column, but only include those rows where the customer column is equal. average_range is required. To calculate the average, Excel sums all numeric values and divides by the count of numeric values. 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". Use of AVERAGEIF Function to Find VLOOKUP AVERAGE in Excel. The issue arises when there are multiple columns with same group as Averageif takes the first column only. Step 1: Type =AVERAGEIF( in an empty cell Step 2: Select the range. You can use the following methods to write AVERAGEIF and AVERAGEIFS functions using VBA in Excel: Method 1: AVERAGEIF Function in VBA. Then enter the Range, Criteria, and Average_range in the Function dialog box and select Done. . =SUM (ROW (A1:A4)) and press CTRL+SHIFT+ENTER from within the formula bar to convert this to an array formula. 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. AVERAGEIFS (C2:C8,B2:B8,”Mumbai”,C2:C8,”>50″) example. While I need 1 conditions_range that is only 1-column-width and 1 average_range that is 5-columns-width. The input in columm A is time. 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. AVERAGEIF (criteria_range, criterion, [average_range]) Let’s look at the first case. AVERAGEIF function for multiple columns. 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. 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 am trying to calculate average based on multiple column. In G1 get the average value for in column C for all seconds matching what is in F1. I'd like. I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. AVERAGEIFS() requires that all the ranges must be the same size and shape. I. Follow. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. 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. It would be best if you store your variables in Cells. 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. How to Calculate Average of Multiple Columns Based on Single Condition 1. Today I will show you a quick and easy way to take the average of a group of data with multiple criteria. 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. criteria_range is the set of categories containing the specific criterion whose average will be taken. The worksheet includes values for the last several years. Note. 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. One or more cells to average, including numbers or names, arrays, or references that contain numbers. criteria_column1 - The data. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. About;. I have a tab with a table. =AVERAGE (column_references) ARGUMENTS. This article explains how to. Average one column based on multiple criteria in other columns; essentially a plural AVERAGEIF, allowing you to add multiple filter conditions to determine what rows to average. INDEX (F2:H5,0,MATCH (A2,F1:H1,0)) Calculate the Average of Result based on the returned column. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. 1 Answer. )] I hope I was able to explain what I want. xlsmWhen I try to drag the formula across though, the start and end cells only move one column over (i. 09-17-2012, 03:12 PM #5. Your usage may be, =AVERAGEIFS (H:H, C:C, C2, G:G, "Buy") Share. Another issueis that some rows not all of the columns in reaction [Hit 1]:reaction [Hit 5] are populated and the blanks should not be included in the average. Are you allowed to add a column to your data for calculating the total_price? For example, column E = Quantity * Price. The VBA help does provide some detailed. 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. Returns the average value for the items in the list, list. 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. Array formula 1: find value with two or multiple criteria in Excel. Trying to run an AVERAGEIFS function across a date variable AND either criteria 1 OR criteria 2 out of a second range. 2. Then add a criteria to averageifs to test column C for 1. 2. As a worksheet function, the AVERAGEIFS. average_range is required. Average for multiple criteria in one column. Only works with number, date, time, datetime, datetimezone and duration values. To get column average or mean from pandas DataFrame use either mean() or describe() method. @TerryW column F is just a list of. One or more cells to average, including numbers or names, arrays, or references that contain numbers. From the selected column, it will search the lookup value to calculate the average of these values. This formula is particularly useful when dealing with large datasets and complex data analysis scenarios. 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. In this case, the AVERAGEIFS function comes as a savior. Unsure where my errors are on this one. In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. With AVERAGEIFS, you can specify multiple criteria to calculate the average only for the values that meet all the specified conditions. It can also be solved. I want to compute the average of every product. I want to accomplish what this formula does, but to include. Using =averageifs with multiple text criteria in different columns. I've found the following post: AVERAGEIFS with multiple criteria on one range In which T. Rather than using AverageIf (), try using Average (If ()) as an array formula for this kind of scenario. criteria_column1 – The. Specify the range to check against the 1st condition (B3:B15 - items). 3. 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. Since your. One approach is based on the AVERAGEIFS function, which is designed to calculate averages using multiple criteria. Data / Get & Transform / From Table/Range. Row 3: 98 – 102= -4. I want to know the average of all Cat that are in the UK only, which would be 3. 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 this example, this is the Score column in the table: =AVERAGEIFS(data[Score], The next two arguments specify the criteria. There is a formula that does this for you called AVERAGEIFS. i have tried multiple ways, including this one: Avg = List. Recall the formula that we used to calculate the average difference between the two columns: =AVERAGE (B2:B11 - C2:C11) This formula works by first calculating the difference between corresponding values in the ranges B2:B11 and C2:C11. Fill one cell depending on value of another. WorksheetFunction. One or more cells to average, including numbers or names, arrays, or references that contain numbers. My first attempt was using a multitude of VLOOKUP functions, but the formulas were only getting messier so I dropped that approach. 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. Then select the merged cells. Ask questions for further explanation. The criterias would be by year (have multiple years), then by Pt type (in or out Patient),. The result returns 7 (average value). We can add as many criteria as needed to determine our final results. 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. AVERAGEIFS requires that all the conditions be true. So I have 3 columns in this example. 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. For example, we are given the total cost of preparation of three items. For example, you may want to find the average of a column only for specific regions or months. I have a spreadsheet with a col of phases (Phase 1, Phase 2, etc. In our following & new dataset, the sales of computer devices of different brands are. Press Enter key. Then I can use =AverageIfs(B:B,[month helper range],1,[year helper range],2007). 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. I need the formula to find the value of "BF3" in every sheet from A3 down to A100 but i need the average value. AverageIFs multiple columns when meets multiple criteria in one column. Description. Column C = 1 if there is a number in it, AND it is a visible row, otherwise column C. Excel begin AverageIF function. You can use the AVERAGEIF function to calculate the average lookup value. g. For. Lolo1313. AverageIf(Range("F5:F30"), "Savings",. This can be a single column or row, or even a combination of multiple columns and rows. This help content & information General Help Center experience. 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. Here's an. I have tried the following formulas. 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). This function can be used to know the average balance in the inactive bank accounts with an updated KYC. One is Name Box and another is Formula Box. 2 Apply AVERAGEIFS Function for Multiple Columns. Improve this answer. Suppose you have a table listing the scores of two tests. In this section, we will use. Let’s determine the average for sales that are greater than $60000. average_column: The data column to average. 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. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. I'm trying to make use of Averageifs with multiple criterias in multiple columns. A not so average solution to an uncommon problem in Excel. To create, select a cell, go to the Formulas tab, and select More Functions > Statistical > AVERAGEIF. 1. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. About. Here is the same formula with an adjusted range to C2:N22. 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. Edit: In the mean time, I have created two helper columns, that are just the Month() and Year() of each row of data. There are two fields in the Insert Calculated Field dialog box. Calculating Average by multiple columns. I work in a call center, and I'm working with a quality report trying to generate an average for each supervisor. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. 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. AVERAGEIF With Multiple Criteria. Below is one more solution for the sameI want to average data in those columns if specific multiple criteria is met in other columns. Therefore the cells in the range are used to calculate the average. Let us see how to apply this function with multiple ranges. 2, and finally averages the values from the range E2:E10 that are in the rows that qualify the above two conditions. The mean() method is used to return the mean of the values along the specified axis. 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". Which can take in multiple criteria to determine what to average. We can use the following formula to do so: =AVERAGE (week1!B2, week2!B2, week3!B2) The following screenshot shows how to use this formula in practice: The “Average Points” column contains the average of the points scored for each player across week1, week2, and week3. As you can see clearly above, in Averageif the criteria range comes first, but in Averageifs it. Result. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. The FREQUENCY function performs a series of "count if" operations based on the arguments provided. I want the average of 'column b' data that corresponds to 'red' and 'yellow'. Would have used AverageIfs in excel for this calculation. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. 0. Let us see how to apply this function with multiple ranges. =AVERAGEIF (B5:E9,"<>0") Next, press the Enter key and this will find the Sales Average inside cell D10. 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. To begin this method, double-click on cell D10 and insert the formula below. Sample usage. RangeForCriteria1: C2 to C8. Range("G:G"), Sheets(wsName). 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 VSTACK function accepts 1 or. WorksheetFunction _ . AVERAGEIF with multiple criteria. Re: averageif across worksheets. AVERAGEIFS uses all criteria you offer to select a number. The AVERAGEIF function calculates the average of cells in a range that meet a specific condition. Let’s follow the instructions below to learn! Steps: First of all, merge cells E5 to E15. 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. The AVERAGEIFS function is a built-in function in Excel that is categorized as a Statistical Function. 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). See the. Averageif multiple columns. Similarly, the function can. Excel: averageif function for more than one cell. Here is a screenshot to clarify. 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 AVERAGEIF function in Google Sheets has the following syntax: =AVERAGEIF(criteria_range, criterion,[average_range]) Let us break down its arguments. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. Returns the average (arithmetic mean) of all cells that. =AVERAGEIF (B:B,F1,C:C)Hi Creative999, AVERAGEIFS doesn't have an OR option. The AVERAGEIFS function calculates the average of all sales values in the range C3:C11 if the corresponding cells in the range A3:A11 (Sales Manager) with six characters; and the corresponding cells in the range B3:B11 (Bonus Date) should be greater than or equal to 10-May-20. Since you only have a single criteria in this case AverageIf should suffice. NOTE: AVERAGEIF is only available in Excel 2007 and later. criteria1 is required. 0. ) – Mrig. 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)))). 0. AverageIFs multiple columns when meets multiple criteria in one column. Step 3: Enter Formula for Calculated Average Field. Most solutions show how to use it with AVERAGEIF() and not AVERAGEIFS(), which has a different structure. To do that you could calculate average by yourself. 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. Under each month I have a reported lab value. When say conditional average, we think of Averageif, but in this sc. 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 2 conditions are mutually exclusive, so you're presenting nothing (null) to the average function (within the AVERAGEIFS). how to use multiple criteria in AVERAGEIF function. AverageIf(Range(" A2:A12 "), "Mavs", Range(" B2:B12 ")) End Sub. 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. Valko proposes =IFERROR (AVERAGE (IF (ISNUMBER (MATCH. 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). 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 approach is based on the AVERAGEIFS function,. That will get you the following table. 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:. Sorted by: 1. The following examples show how to use each formula in practice. AVERAGEIF with multiple criteria: AVERAGEIFS. The average function evaluates the zero but ignores the blank cell and the. 0. What I need is a formula to provide the average temperature for that location between the two dates. 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. I want to average column K if certain criteria are met in Columns G & I. Select the columns or rows you want to compare. Example 2: To get the average price of the product with Category ID 1 and having a Price greater than Rs 500/-. Range is the range of cells to apply a condition to. 67. My dataset is about 15,000 rows, and the values I want to average (in a separate list) amount to around 2,000 values - using something like AVERAGEIFs would suffice, but it's going to take me an age - I'm wondering if there's a good way around. AVERAGE using multiple criteria from. In such cases, you can use the AVERAGEIF and AVERAGEIFS functions. Note that, we have changed the column 1 title from Month to Month Number so as to include the numbers inside the formula. Joined Dec 15, 2005 Messages 1,227 Office Version. I need to average cells which are labeled in a different column. MATCH (A2,F1:H1,0) Return the data in the column. The formula can use any worksheet function and use any fields from the data source. The sum range argument in the SUMIFS function cannot have multiple columns. Firstly, select your entire dataset and press the keyboard shortcut. 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. in this example Temprange. so on so forth. Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. Here, cell range B5:B13 is selected as criteria_range1, and cell range C5:C13 is selected as criteria_range2. I only added some of January's data and not all the months for the post. But we can’t get the average with the AVERAGEIF function when it comes to multiple criteria. Method-2: Finding Average If Cell Contains Text (Multiple Text Criteria) 2. To define criteria, AVERAGEIFS supports logical operators (>,<,<>,=) and wildcards (*,?,~), and can be. Criteria Required. AVERAGEIF Google Sheets Syntax. Hi All, I am trying to calculate average based on multiple column. . 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. In this instance, select B2 through B16, to identify the Age column as the range. 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. I’m trying to get an average of data in column C based on the corresponding time range in column A. I have to find average number of days where Status = new or open, Severity = 4, and Area = Apps. Multiple columns. 2. 5. Subsequently, insert the following. By thatguytg53 in forum Excel Formulas & Functions Replies: 11 Last Post: 03-11-2014, 06:17 PM. Mr Excel Questions 3. 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. AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF function syntax has the following arguments: Range Required. Example 2: Using AVERAGEIFS for 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. Then select the merged cells. In our following & new dataset, the sales of computer devices of different brands are presently based on the months. Windows; Dec 6, 2011. The above formula. Syntax AVERAGEIF (range, criteria, [average_range]) The AVERAGEIF. In Excel, it’s not possible to use the AVERAGEIF () function to calculate an average value using multiple ranges. After this, you. In this video we will be learning calculating conditional average from multi column data. Then we create a COUNTIF function to count the number of records that appear more than once: =count. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column - The data column to average. You can apply the AVERAGEIFS function on a single column to calculate the average. Columns(20), _. I was making the formula in a cell referencing an item that is not currently listed in the data set. Average. You can add as many criteria_range,criteria in AVERAGEIFS. 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. The function was introduced in Excel 2007. Sample Usage. 0. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. As Long Dim TempRange As Range Dim TempArr As Variant Dim MyArray As Variant a = Application. This guide will demonstrate how to calculate the average with multiple criteria in Excel. Blank and text values are ignored. 1. If the list is empty null is returned. To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). Excel AverageIf to get total average of multiple rows from a table. Type "=AVERAGEIF (" into a blank cell. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. Rajesh Sinha. 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. I feel like I am missing something simple or easy that is not locking in the specific data I would like to compute. "<1000") Select the range of cells you want to average. Finally, we'll take a look at Excel's AVERAGEIFS function. In this video show you how to calculate the average values across multiple columns with a filterin. Criteria is the condition to apply, along with any logical operators that are needed. Note the lookup_value in XLOOKUP is 1 since the. Both of these ranges contain only one column. 0. Averageifs with OR criteria. It is one or more cells to average. Full formula should look like:It will also do that if there previous cell is equal to "". FORMULA. SelectColumns ( #"ENG -. Yields the answer 6, so AVERAGEIFS (or an alternative formulation) should be able to produce an average for those 6 cells. This help content & information General Help Center experience. AVERAGE using multiple criteria from multiple columns. I've tried to do an averageif formula and all sorts but it won't work with the categories. 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. . 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. Using the Power Queryfeature of Excel can be an efficient way to do this. When the criterion is a number value, simply enter the. The AVERAGEIF formula within the Google Sheets application. It allows you to average data in one range of cells where the data in another range matches a certain criteria. AVERAGEIF(criteria_column, criterion, average_column) criteria_column: The data column to check against `criterion`. . Add a comment. 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. Type the formula ‘=VSTACK (‘ to start the VSTACK function in Excel. There are multiple of each phase and I want to calculate the average % for each phase. =AVERAGEIFS (G:G,G:G,">0",A:A,"PHA") Spot on, Thank you. g. criteria1 is required. 2. . AVERAGEIF function on different cells in different sheets. I'm wondering if there is a way to nest a vlookup or index match or anything like that in the AVERAGEIFS that read the criteria column heading and criteria in a cell (or 2 if they need to be separated) to be added to the AVERAGEIFS. 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. 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). 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. In both the VBA and Excel examples the formula averages all of the numbers in columns C and D. column_references: The columns which you want to average. Type "," and then enter the criteria you want to use (e. The formula makes use of a virtual array, containing the values of column C and the output of the mmult () function. Matching and returning values to populate column. And because of this when You are doing Your AVERAGEIFS function it will result in a #DIV/0. Example 1: AVERAGEIFS with One Character Column. 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. Originally Posted by daddylonglegs.