For example, you may want to find the average of a column only for specific regions or months. e. ) 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. We can add as many criteria as needed to determine our final results. Type or copy this formula: =AVERAGE (A1:B7,C2:D5,E2:F6) Using commas to separate the non-contiguous ranges in the formula, you can add as many ranges as you need. Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2) In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'. It can take arguments where the first argument is the range column in which the average needs to. The formula can use any worksheet function and use any fields from the data source. AVERAGEIF with multiple criteria: AVERAGEIFS. AverageIFs multiple columns when meets multiple criteria in one column. Sample usage. I've also tried inserting a new row below the dates (i. RangeForCriteria1: C2 to C8. 0 Excel: averageif function for more than one cell. It is important not to use simply AVERAGE function but divide SUM by SUM (COUNT) instead. 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. What I'm trying to do is create a new spreadsheet that will give me an average of multiple weeks for specific individuals. From the list of products, I took the unique values for a certain month. g. ) Otherwise, when you propogate down for subsequent row averages,. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory,. Change the Table Name in Line 2 to reflect the actual table name in your worksheet. 0. Applying Multiple Criteria in AVERAGEIFS Function. how to use multiple criteria in AVERAGEIF function. 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. I’m trying to get an average of data in column C based on the corresponding time range in column A. The total amount for the groceries is $78. So if I need, say, a. No, just that if the first range is 1 column by 100 rows, the other ranges should also be 1 column by 100 rows. "<1000") Select the range of cells you want to average. To get an average of data in a column with multiple criteria the following formulae may be used. 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. Criteria Required. Average multiple values from multiple columns in Excel. Group Average A 113. Thread starter mgirvin; Start date Dec 6, 2011; M. 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. if column G is the sum of columns A and B and column H is the sum of columns C and D you could use this version3. Column C = 1 if there is a number in it, AND it is a visible row, otherwise column C. 6. Let’s follow the instructions below to learn! Steps: First of all, merge cells E5 to E15. Each of my cells is 10 cells apart. I used the AVERAGEIFS command but I recieve a message that "excel the formula you typed contains an error". One last one, just need to add in one further criteria to the above. 3. This is the syntax of the AVERAGEIFS function. 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. To Blue_Elliot's point, an AVERAGEIF function would do what you are wanting. I need to average cells which are labeled in a different column. In this video show you how to calculate the average values across multiple columns with a filterin. are in a group of functions that do not support 3D. = 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). 1. 0. For example, you may want to find the average of a column only for specific regions or months. In this instance, select B2 through B16, to identify the Age column as the range. The criteria values are entered on the. =AVERAGE (IF ( (C2:C5)* ( (D2:D5="US")+ (D2:D5="Germany")),B2:B5)) After array-entering the formula, the formula bar will show. The above formula. (2) Work out the count for each column separately and total them up. Here is a screenshot to clarify. I want to calculate the average of the last column (SLA in days) for the lines. Averageifs with OR criteria. One or more cells to average, including numbers or names, arrays, or references that contain numbers. How do I `SUM` by multiple columns in Excel. Then add a criteria to averageifs to test column C for 1. The 2 conditions are mutually exclusive, so you're presenting nothing (null) to the average function (within the AVERAGEIFS). AVERAGEIF is the best answer for Excel 2013+. To average numbers based on multiple criteria, you can use the AVERAGEIFS function. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. 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". Hope that helps. AVERAGE using multiple criteria from. The criterias would be by year (have multiple years), then by Pt type (in or out Patient),. The AVERAGEIF function calculates the average of cells in a range that meet a specific condition. Any record appearing more than once (>1) is considered a duplicate. Second part SUMPRODUCT ( (C3:M17<>"")* (A3:A17=750)* (C1:M1=15)) gives. Use the following formula for G1 and copy down to the bottom of your list in column G. Sub Averageif_Function() Range(" E2") = WorksheetFunction. I'm sure I'm missing something obvious here, but just to confirm, B3 in this case is an integer (700), column K in the relevant table is the correct reference, and includes figures greater than 700, column G in the table is the correct reference and includes multiple values that are greater than zero, and the formula: 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 column where the Team column. Steps: 1. So for example the home. First the Formula you have will not return the true average of the found criteria but the average of the average. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Otherwise it increases the value of the previous cell by 1. 1. Now, write down the VBA code to calculate the average of multiple ranges. 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. 1 to 127 ranges in which to evaluate the. 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. Trying to run an AverageIfs that includes multiple criteria and a calculation. . Aug 2, 2017 at 10:41. AVERAGEIFS with multiple criteria on one range Originally Posted. 2. So for example, say I want the average between 09:00:00 and 09:30:01. Formula to create a cartesian product from two data sets in a spilled range. In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. Columns G & I have text values and Column K is a number value. AVERAGEIFS(average_column, criteria_column1, criterion1, criteria_column2, criterion 2) average_column – The data column to average. For older versions, you'll need to use a third column, C. The Excel AVERAGEIFS function returns the average of cells that meet multiple conditions, referred to as criteria. In my small example below with 3. In this example, the goal is to calculate a monthly average for the amounts shown in column C using the dates in column B. 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. 1. Average with multiple conditions. I used the TODAY function to indicate 30. 0. This can be accomplished by using multiple AVERAGEIFS function to the formula. Cell C3 sums all the numbers in range A1:A5:. For convenience all data is in an Excel Table named data in the range B5:. AVERAGEIF is only available in Excel 2007+. A | B 1 | Name 1 | Value 1 2 | Name 2 | Value 2 3 | Name 2 | Value 3 4 | Name 3 | Value 4. The formula: =1/ (critRng1=TRANSPOSE (crit1))*1/ (critRng2>=DATE1)*1/ (critRng2<=DATE2)*avgRng. I have tried =Average (IF ( (A:A="Apps")* (B:B="4")* (C:C="new")+ (C:C="open. edited Feb 8, 2020 at 16:31. The Convert Text to Columns Wizard –. In setting multiple criteria using an AVERAGEIFS function we can easily calculate sales made by Peter on the 10th of November. For example, the following formula with return the sum of all values in cells C2:C9 that are greater than or equal to 200 and less than or equal to 300. 4 Answers Sorted by: 1 The AVERAGEIFS criteria come through as AND conditions. In this case, we're checking cells C2 to C17, for all values greater than $100. criterion: The pattern or test to apply to `criteria_column`. You can use the AVERAGEIFS() formula to achieve what you want. You can use AVERAGEIFS formula for multiple conditions. 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. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). Columns(20), _. ). We have marked both criteria with a Yellow color. It is one or more cells to average. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. For. 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. Averages the values in the "Quantity Ordered" column, but only for the rows where the "Item" is "Shirt". FORMULA. Select the columns or rows you want to compare. In this video show you how to calculate the average values across multiple columns with a filterin. If I understood correctly, you are trying to choose dates before 4/1/14 OR after 8/30/14. But as demonstrated below, they do not. Excel: How to Use AVERAGEIFS with Multiple Ranges (different columns) 0. By jeroenft in forum Excel General Replies: 4 Last Post: 02-03-2015, 04:58 PM [SOLVED] AverageIF using multiple criteria in different columns in multiple worksheets. goaldFor column i want to add-up all values and devide by the number of values. . Example 1: AVERAGEIFS with One Character Column. That seems a bit complex so I tried an other approach: do the average value of the first entry for each name. This code works when considering only one metric: +QUERY(my_table," SELECT Col1, AVG(Col4). Criteria Required. Unsure where my errors are on this one. The answer should be 4. 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. Joined Dec 15, 2005 Messages 1,227 Office Version. Averageifs with Or. 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. 0. 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,. The statement is as follows (they way I thought it would work, anyway): =AVERAGEIFS(J:J,F:F,"Inactive",D:D,"Equity",D:D,"Fixed Income"). Perhaps try something like this: Code: Sub example () Dim av As Double av = Application. Average for multiple criteria in one column. INDEX (F2:H5,0,MATCH (A2,F1:H1,0)) Calculate the Average of Result based on the returned column. 2. WorksheetFunction. AVERAGEIF vs AVERAGEIFS While Considering Criteria. In the Combine Worksheets – Step 1 of 3 wizard, check the Consolidate and calculate values across multiple workbooks into one worksheet. . We know that an average is a sum divided by a count. . How to Calculate Average of Multiple Columns Based on Single Condition 1. I'm guessing this is probably why I'm getting errors. Another way to open the Visual Basic Editor is to simply right-click on the sheet and select View Code. Define a range name to non-contiguous ranges. What it Returns. 3 B 213. Note. The sum range argument in the SUMIFS function cannot have multiple columns. In Averageif, you can only use one single criterion whereas in Average ifs you can use multiple criteria. Syntax =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2],. The AVERAGEIF formula within the Google Sheets application. What you can do is use INDEX and some MATCH to make a dynamic function that will identify the cells to be averaged. 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. Excel: Averageifs from a table with columns. My sheet has 6 different Categories, and each category has a MISC subcategory, if I leave the formula as is [=AVERAGEIF(B:B,BN21,AS:AS)] it will average all the cells that correlate with the word MISC populated in column B. I have manually averaged some of the formulas to double check them and they are not computing accurately. Type the formula ‘=VSTACK (‘ to start the VSTACK function in Excel. I am trying to calculate average based on multiple column. AVERAGEIFS works even if there are blank or text values in the measured column. In our following & new dataset, the sales of computer devices of different brands are presently based on the months. Averageifs with Or. The. In such cases, you can use the AVERAGEIF and AVERAGEIFS functions. criterion is the condition to be tested. AVERAGE takes multiple arguments in the form number1, number2, number3, etc. Type "=AVERAGEIF (" into a blank cell. Average Based on Multiple Criteria on a Single Column. Excel begin AverageIF function. find average for multiple values in rows and columns in Excel. I only added some of January's data and not all the months for the post. I need to calculate an average when two criteria are met, but these criteria are in two separate columns. This can be a single column or row, or even a combination of multiple columns and rows. To solve your problem, you need to return an array from which you can return the relevant values. Matching and returning values to populate column. AVERAGEIF with multiple criteria. Cross posted multiple sites. average_range is required. The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. Result. Enter the following formula in S2: =--(R2>0)Average for Multiple Criteria. 0. The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not blank. Below is one more solution for the same I want to average data in those columns if specific multiple criteria is met in other columns. At this time, click on cell D17. The result returns 7 (average value). The average function evaluates the zero but ignores the blank cell and the. 2. The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. Method-3: Averaging Cells That Match Text Exactly. Coachcasa New Member. Jul 23, 2014 #1 I am trying to find. 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. 1. "A" and "G") may be zero. New column contains the formula IF (OR (AND (ColA >= Date1, ColB <=Date2), AND (ColA >= Date3, ColB <= Date4)),TargetCol,"") This will return the value to. I took it for granted that #N/A errors in one of the ranges would cause a problem. 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. 0. AVERAGEIFS() requires that all the ranges must be the same size and shape. 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. Breaking my head over this, time to look for help :(I have a sheet with raw data, as illustrated below. averageifs w/ multiple average ranges in different columns. Excel Sum Based on Multiple Column and Row Criteria. Actual Calculation of this Formula: For each time F does not equal 0 (even when E of the row does not equal "A"), return to SUM the AVERAGE of F (where E="A", even average in the zeros): 9* (18/5) [missing red highlighting in row 25]. 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. 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 this short tutorial video, you'll learn how to find the average sales from a data set, based upon two different criteria. Array formula 1: find value with two or multiple criteria in Excel. To accomplish the task, you build an AVERAGEIFS formula with two criteria: Define the range to average (C3:C15). 2. OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i. I think the best way to do this is to just calculate the average the old fashioned way:. So, I'd want the average for fruit, (so the orange, apple and banana columns), but only if the value is not 0. Use SUMPRODUCT:AVERAGEIFS returns the average of all cells that meet multiple criteria. g. AVERAGEIFS Match multiple criteria. 3. Applying AVERAGE and FILTER Functions Applying AVERAGE and FILTER functions is one of the smartest ways to calculate. To get column average or mean from pandas DataFrame use either mean() or describe() method. Name Prior. 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). Range("M:M"), _ Sheets(modelName). 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. AverageIFs multiple columns when meets multiple criteria in one column. – Gandalf96. 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:. The AVERAGEIF function calculates the average of cells in a range that meet a specific condition. . 9. 2. AVERAGEIFS function in Excel is used to find the average from the target range of cells when more than one condition is met. 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. I'm trying to get excel to give me the average of multiple objects if they meet a 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. Stack Overflow. AVERAGEIF based on multiple columns in google spreadsheet. 0. The AVERAGEIFS uses the following syntax: average_range, criteria_range1, criteria1, criteria_range2, criteria2, etc. NOTE: AVERAGEIF is only available in Excel 2007 and later. 2. Criteria Required. Finally, we'll take a look at Excel's AVERAGEIFS function. Long story short, I'm trying to average multiple columns of number data based upon if the column matches criteria. AVERAGE: The AVERAGE function returns the numerical average value in a dataset, ignoring text. 1 Answer. criteria_column1 - The data. 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 using OR function. Thread starter conlan; Start date Jul 23, 2014; C. Then, click on Visual Basic or press Alt + F11 to open the Visual Basic Editor. 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. Count from multiple columns with multiple conditions in Excel. So, one thing I'm thinking is that your lookup has no records or you have a mispelling. As Long Dim TempRange As Range Dim TempArr As Variant Dim MyArray As Variant a = Application. AVERAGEIF(criteria_column, criterion, average_column) criteria_column: The data column to check against `criterion`. WorksheetFunction. Make sure to start the formula with Equals Sign. 2. Below is one more solution for the sameI want to average data in those columns if specific multiple criteria is met in other columns. 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. And the. g. As you have found, AVERAGEIF does not work across multiple sheets; nor do COUNTIF, COUNTIFS, SUMIF etc. Using entire column references within an array formula is not at all a good idea. To do that you could calculate average by yourself. We can create the following macro to perform this AVERAGEIF function: Sub Averageif_Function() Range(" E2") = WorksheetFunction. Most solutions show how to use it with AVERAGEIF() and not AVERAGEIFS(), which has a different structure. Countif with average. If you need to add two criteria, for example the average quantity of shirts that Joe orders, use a combination of AVG and. MATCH (A2,F1:H1,0) Return the data in the column. AVERAGEIFS takes care of multiple conditions, but single average_range that in AVERAGEIFS should be the same size as conditions_range. Mr Excel Questions 3. Read below for details. Is there a way to do. Average for multiple criteria in one. So all rows with the same combination of. Sorted by: 1. Use this array formula: =AVERAGE (IF ( (ISNUMBER (MATCH (B2:B10, {"Emp1","Emp2","Emp3"},0)))*. AVERAGEIFS(table_name!price, table_name!fruits, “Apple”, table_name!inventory, “<30”) Syntax. Example: Average Revenue by "FY Quarter, Month, City and product" and then calculate "Max Sales by FY quarter, Month and city" and finally. 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. In the UI, open the Advanced Editor. Express the 1st condition ("math" or F3 - the target item enclosed in quotation marks or reference to the cell containing the item). 2. 0. Average. In the next screen shot, the AVERAGEIFS formula checks for Pen in column A, and a quantity greater than or equal to 10, in column B. This step by step tutorial will assist all levels of Excel users in comparing these functions to deal. Forum Guru Join Date 04-13-2005 Location North America MS-Off Ver 2002/XP and 2007 Posts 15,616AVERAGEIF doesn’t work on a non-contiguous range. 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. A not so average solution to an uncommon problem in Excel. Yields the answer 6, so AVERAGEIFS (or an alternative formulation) should be able to produce an average for those 6 cells. Originally Posted by daddylonglegs. The result is given in the same datatype as the values in the list. 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. Let’s use the procedure discussed in the following section. in C2 it needs to calculate the average of F2-F6 since E2-E6 is equal to, or between 140 and 142 (Interval in cells A-B). The AVERAGEIFS evaluates the cell range that contains TRUE as 1, and FALSE as 0. RangeForCriteria1: C2 to C8. It can also be solved. However, when I try a range with multiple columns, Excel only seems to include rows if the code appears in the first code column. I was using AVERAGEIF to calculate the average percentage for each Phase: AVERAGEIF(Table1[PHASE], "Phase 1",. I have a tab with a table. Next, enter a descriptive name in the Name Box and input the formula in the Formula Box. 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. which is the call durations column in the table: =AVERAGEIFS(data. The criteria in the form of a number, expression, cell reference, or text that defines which cells are. In this example only the rows where the item is. In this example, we will demonstrate the use of the AVERAGEIFS function based on multiple criteria. Select the range of cells you want to evaluate. Specifying Multiple Range. Excel AVERAGEIFS Function. I want to average column K if certain criteria are met in Columns G & I. A not so average solution to an uncommon problem in Excel. E. 1. Assuming you have a list of items in column A, planned sale figures in column B, and actual sales in column C. 📌 Steps: First and foremost, specify your conditions in cells B17 and C17 for better visualization. The mistake in the formula is that it only takes one argument for the range. e. 2. I tried this but failed: =AVERAGEIF(range1; criteria 1; range 2; criteria 2; average range). This would take your multiple columns and make them rows. With AverageIfs the conditions are used with an AND, not with an OR in the logic. 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. Thread starter Coachcasa; Start date Feb 1, 2021; C. 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. ) and col of percentage values for each phase. 0. To get an average of data in a column with multiple criteria the following formulae may be used. 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. That’s my simple answer to the above question. ; The function treats the cell as a ‘0’ value if any cell in the criteria range is empty. The AVERAGEIFS function in Excel gives the option to insert multiple ranges in order to find the average based on specific criteria. AVERAGEIFS across multiple worksheets. 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.