Excel help on combination of Index - match and sumifs?

I have these three tables as shown in this image below: SalesOrderTable, OrderAdjustmentTable, and TotalSalesTable.


Each order number in the SalesOrderTable represent an order number for a product from the same invoice. It has A,B, or C if an invoice is made for more than one product, but not if it is only made for one product. E.g.: Order 1703 has two products, so it has A and B at the end of each order number. Order 1704 has an order for Apple only, so it doesn’t have any letter at its end.


Should there be any adjustments for each order number, they are inputted manually in the adjustment columns.


In this table, all amount of total sales and adjustments for the same order number are totaled. So order for 1705 are combinations of 1705A, 1705B, 1705C.

Here are my current formulas, all of them produce error messages or don’t calculate correctly.

Order total amount column:

=SUMIF(SalesOrderTable[Order Number], LEFT(G4,LEN(SalesOrderTable[Order Number])-1),SalesOrderTable[Order Sales Amount])

Order Total Adjustment Column:

=sumifs(OrderAdjustmentTable[#All],OrderAdjustmentTable[Order Number],MATCH(B19,LEFT(SalesOrderTable[Order Number],LEN(SalesOrderTable[Order Number])-1), 0), "*Adjustment",OrderAdjustmentTable[#All])

I know it may have something to do with match and sumifs, but all the formulas I made led to error messages. Anybody can help me with what I did wrong with these formulas above, and how do I fix them? I am at my wits end here and will appreciate any advice given. Thanks a lot!


What I want to achieve is to fill:

The order total amount column in TotalSalesTable with all orders that have the same order number, irrespective of their ABC. So Total amount for 1705 will be the sum total of order amount for 1705A, 1705B, 1705C.

For the order total adjustment, I want to have all the adjustments value for the order number column in TotalSalesTable from OrderAdjustmentTable, irrespective of their ABC. So total adjustments for 1705 in TotalSalesTable will be 1705B and 1705C.


In messing around with this for a second, putting LEFT in SUMIF seems to cause issues (at least on Mac).

I'd recommend calculating Order Number without the letters in a separate column first. LEFT returns a character string so I had to wrap it in VALUE so that SUMIF will match. Since all of yours are four digits, I just used VALUE(LEFT([@[Order Number]],4))

Then it's just standard SUMIF notation:

=SUMIF(Table1[Base Num],[@[Order Number]],Table1[Order Sales Amount])

Example: https://www.dropbox.com/s/yj6ktl14jqglxsm/Example.xlsx?dl=0

There are various ways you could get the order number, but you have to be careful with your example:

LEFT(G4,LEN(SalesOrderTable[Order Number])-1) because this will work correctly for 1703A and 12990B, but will drop a number from 1701.

EDIT: This is one way to get order number if your number of digits varies: =IFERROR(VALUE(A2),VALUE(LEFT(A2,LEN(A2)-1)))

This says, "if VALUE(A2) returns an error (meaning it has a letter in it), remove the last character, otherwise, use VALUE(A2) since it's already a number."

As long as there are no orders that overlap with and without letters (for instance, the SalesOrderTable having records for 1703, 1703A, and 1703B), the following formula should work in cell C19, from where you can copy it down:

Order Total Amount:

=SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19)+SUMIFS(SalesOrderTable[Order Sales Amount],SalesOrderTable[Order Number],$B19&"*")

This formula adds together the exact and approximate matches, but like I said if any orders both have and are lacking a letter something will likely get duplicated/double counted. You should also be able to replicate the formula for the Order Total Adjustment Column.

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

  • 4组EXCEL中常用的嵌套函数!
    你会在EXCEL中使用嵌套函数吗?EXCEL功能的强大离不开嵌套函数的存在。但是当你看到各种复杂的嵌套函数是否望而却步呢?本文带你揭秘如何使用嵌套函数,并提供最常用的嵌套函数供收藏~ 所谓的嵌套使用函数,指的是多个函数同时使用,其目的就是为实现某种复杂的统计功能。一个函数中通常有参数和常数,常数不能用其他的方式替换,而参数则可以嵌套进去另外一个函数,这样参数中的值就是由另外一个函数生成的。 函数可以一层又一层的嵌套,但是在实际工作中,不要过于追求函数和复杂程度,解决问题才是关键,解决方式当然是越简单越好。 本文以四组EXCEL中最常用的嵌套函数为例,帮大家理清使用嵌套函数的思路。 01 Index函数 & Match函数 MATCH(lookup_value,lookup_array,[match_type]):返回符合特定值特定顺序的项在数组中的相对位置。 INDEX(array,row_num,[coulumn_num]):在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。 Index和Match组合可以根据条件查找字段,并且可以在条件改变后自动更新结果。除了可以实现VLOOKUP函数的查找功能外,还可以实现逆向查找,从右向左查找数据也轻松搞定。 我们来举个例子。下方是一张学员成绩表 现在想要实现,根据学员姓名查找其他相关信息,例如 在I3单元格输入学员姓名
    问题 根据几个网站,SUMIFS和COUNTIFS比SUMPRODUCT更快(例如:http://exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html)。 我有一个工作表,其中行数未知(约20万),并且我正在用这些数字计算性能报告。 我有6000倍以上几乎相同的SUMPRODUCT公式,但每次都有几处不同(仅条件会发生变化)。 这是我得到的一个例子: =IF(AFO4>0, (SUMPRODUCT((Sheet1!$N:$N=$A4) *(LEFT(Sheet1!$H:$H,2)="1A") *(Sheet1!$M:$M<>"service catalog") *(Sheet1!$J:$J="incident") *(Sheet1!$I:$I<>"self-serve") *(Sheet1!$AK:$AK=AFM$1) *(Sheet1!$E:$E>=$E$1) *(Sheet1!$E:$E<$E$2)) +SUMPRODUCT((Sheet1!$AJ:$AJ=$C4) *(LEFT(Sheet1!$H:$H,2)="1A") *(Sheet1!$M:$M<>"service catalog") *(Sheet1!$J:$J="incident") *(Sheet1!$I:$I="self
  • Excel - SUMIF INDEX and MATCH
    I'm stuck with a sumif formula. I think if I used some mix of index and match I could get it to work but I've been unable to do so for the past hour! I need to sum from a table of numbers depending on the house number and 2 dates. For example, I need to sum the numbers for house 1 between dates 08-05-17 and 13-05-17. My previous experience with index and match is that I've only every used it to get a single specific digit.
  • Aggregating data using INDEX MATCH MATCH or SUMIFS
    I'm trying to create an Excel formula that is able to sum multiple rows in a table, where the rows and column to be summed are determined by the contents of other cells. Ordinarily I would use Index Match Match to achieve this, but the multiple rows summation has left me stumped. I've seen a couple of examples on here of Index Match with a SUMIFS formula, but nothing that pairs this with Index Match Match. I have two tables on different Excel sheets. The first one looks a little this (the actual table is 105 columns x 200 rows): That is from a sheet called "Firm Cost Summary". Row 4 contains a
  • 如何在Excel中的数组上使用VBA来执行SumIf(How to perform SumIf using VBA on an array in Excel)
    问题 我正在尝试提出一种最快的方法来在Excel中对具有大约10%的数据集执行SumIf函数。 110'000条线。 我想出了三种方法,但是没有一种令人满意。 这是我尝试的第一个:PC上的执行时间为100秒! Sub Test1_WorksheetFunction() Dim MaxRow As Long, MaxCol As Long Dim i As Long Dim StartTimer, EndTimer, UsedTime StartTimer = Now() With wsTest MaxRow = .UsedRange.Rows.Count MaxCol = .UsedRange.Columns.Count For i = 2 To MaxRow .Cells(i, 4) = WorksheetFunction.SumIf(wsData.Range("G2:G108840"), .Cells(i, 1), wsData.Range("R2:R108840")) Next i End With EndTimer = Now() MsgBox (DateDiff("s", StartTimer, EndTimer)) End Sub 这是第二种方法:执行时间更好,为55秒 Sub Test2_Formula_and_Copy() Dim MaxRow As Long
  • 如何将小计和 sumif 与一系列标准相结合(How to combine subtotal and sumif with a range of criteria)
    问题 我正在尝试创建一个公式来组合SUMIF (使用一系列标准)和小计。 SUMIF语句: =SUMPRODUCT(SUMIF(E:E,O2:O21,G:G)) 我已经开始制作SUMIF小计: =SUMPRODUCT(SUBTOTAL(9,OFFSET(G2,ROW(G2:G5000)-ROW(G2),0)),(E2:E5000=O2:O21)+0) 最后一条语句返回#N/A错误。 我很感激你们能提供的任何意见。 谢谢你。 例子: Order | Date | Mach | Rem -------|-------|-------|------- A |01/20 | 01 | 1200 B |01/11 | 02 | 400 C |01/21 | 01 | 420 D |01/28 | 04 | 1180 E |01/20 | 01 | 1200 F |01/11 | 04 | 400 G |01/21 | 03 | 420 H |01/28 | 04 | 1180 不确定我的桌子是否正确,所以这是我所看到的图像。 我想要做的是根据日期进行过滤——比如说,所有在 1/21 之前发生的订单——然后给出机器列表中的剩余数量。 因此,如果我想要来自机器 1 和 4 的总数,那么该表的输出(打开日期过滤器)将是 2800,求和顺序 A、D、E 和 F。如果关闭过滤器,它将是 5580。
  • 为 SUMIF 函数选择命名范围的特定列(Selecting a Specific Column of a Named Range for the SUMIF Function)
    问题 我正在尝试创建一个 SUMIF 函数,该函数动态地将 Excel 工作表中命名范围的特定列中的值相加。 当没有命名范围时,很容易做到这一点: 该公式挑选出名称中包含“伦敦”的所有单元格,并汇总与伦敦相关的费用。 我想要做的是使用一个名为 TripsData (A2:B5) 的命名范围,并告诉 SUMIF 函数对这个范围的第 2 列中符合在其名称中包含伦敦的标准的条目求和。 如何在不需要为第 2 列创建第二个命名范围的情况下完成这项工作,只需告诉 Excel 在此命名范围的指定列内查找? 索引/匹配仅返回一个值,因此当有多个单元格名称中包含 London 时,该值不起作用。 谢谢你的帮助! 回答1 使用INDEX来引用命名范围内的特定列(它可以引用一整列),像这样 =SUMIF(TripsData,"*London*",INDEX(TripsData,,2)) 回答2 如果将数据转换为 Excel 表格对象,则完全可以在没有任何命名范围的情况下执行此操作。 选择范围内或整个范围内的任何单元格,然后单击“插入”>“表格”或按 Ctrl - T 。 将出现一个对话框,询问您的表格是否有标题。 你的可以。 现在,您可以通过固有名称引用表及其列,并像这样构建公式: =SUMIF(Table1[Expense],"*London*",Table1[Cost]) 当然,即使在公式就位之后
  • Excel Q - 带有二维数组的 SUMIFS(Excel Q - SUMIFS with a 2 dimensional array)
    问题 我有一个二维数组:水平轴上的日期和垂直轴上的标识号。 我想要以特定日期和 ID 为条件的总和,我想知道如何使用 SUMIFS 执行此操作。 出于某种原因,似乎我不能,因为数组是二维的,而标准范围是一维的。 任何人都可以就我可以使用的其他公式给我任何建议吗? 换句话说,我想添加满足我选择的ID和日期的值; 有一个或多个数据点满足条件。 这就是 SUMIF 函数相关的原因。 回答1 有了这些数据,您将无法使用 SUMIF 论坛。 这是您可以使用的公式: =SUM(IF($B$2:$B$6=C9,IF($F$1:$K$1=B9,$F$2:$K$6))) 在适当的地方更改地址,并确保按 CTRL + SHIFT + ENTER 输入地址。 您还可以使用以下公式来避免按 CTRL + SHIFT + ENTER: =SUMPRODUCT(($B$2:$B$6=C9)*($F$1:$K$1=B9)*$F$2:$K$6) 回答2 假设您正在寻找 ID 和日期的交集,您可以使用以下内容: =INDIRECT(ADDRESS(MATCH([ID Number],A:A,0),MATCH([Date],1:1,0))) INDIRECT允许您以纯文本形式输入地址并返回值 ADDRESS将行和列的数字转换为常规地址 MATCH查找给定值在行或列中的位置。 回答3
  • Excel - 比较两列,如果匹配,则从匹配的行中减去其他两列的值(Excel - compare two columns, if match subtract values of other two columns from matching rows)
    问题 我正在尝试比较单元格中相同值的两列。 并非所有人都匹配。 对于那些匹配,即列 B1 中的单元格匹配列 D2 中的单元格,我想减去 A1-C2。 A 和 B 来自一个数据池,C 和 D 来自另一个。 应该比较它们,如果字符串匹配,则减去值。 谢谢 回答1 要获取匹配字符串的列表,请使用此公式。 我把它放在F1: =IFERROR(INDEX($D$1:$D$500,AGGREGATE(15,6,ROW($1:$500)/(COUNTIF($B$1:$B$500,$D$1:$D$500)),ROW(1:1))),"") 并尽可能复制。 此公式仅适用于 2010 或更高版本。 如果您有 2007 年或更早的版本,则将其替换为以下数组公式: =IFERROR(INDEX($D$1:$D$500,SMALL(IF(COUNTIF($B$1:$B$500,$D$1:$D$500),ROW($1:$500)),ROW(1:1))),"") 离开编辑模式时,您需要使用 Ctrl-Shift-Enter 而不是 Enter 来确认它。 然后在 G1 我把: =IF(F1<>"",SUMIF(B:B,F1,A:A)-SUMIF(D:D,F1,C:C),"") 并复制了相同数量的行。 回答2 看看下面的公式。 它可能会帮助你。 E1单元格中的公式: =IF(ISNUMBER(MATCH(D1,$B
  • 在 VLOOKUP/INDEX-MATCH excel 函数中总结相似的值(Sum up similar values in VLOOKUP/INDEX-MATCH excel functions)
    问题 我在 excel 2010 中遇到了 vlookup/index-match 公式的问题。 我有一个表,其中包含名称列中的重复值。 我在另一张表格中有一张表格,其中引用了第一张表格中的名称和金额。 如果原始表中存在重复名称,则应将金额相加。 例如,看看下面的图片。 ABC 是一个重名,它的数量应该为零。 查找名称 ABC 时,如何使用 Vlookup/索引匹配公式在结果中显示“0”? 在目标表中,该名称仅出现一次,因此我不想在 dest 表中添加另一个 ABC。 谢谢 ! 罗尼。 回答1 VLOOKUP和/或INDEX / MATCH只会返回一个条目(第一个匹配项)-因此您不能将其用于求和(除非在数组公式中)。 但是,使用SUMIFS (或在 Excel 2003 和更早的SUMIF )将正是您所需要的:所以在 F2 中,您可以编写: =SUMIFS($C$2:$C$6,$B$2:$B$6,E2)
  • 创建 excel Do Loop 时遇到问题(Trouble creating an excel Do Loop)
    问题 我有需要为其创建某种循环的数据。 基本上,我正在使用字符串搜索来搜索包含我需要的信息的列,然后根据各种条件进行求和。 我的问题是数据以月份分隔(使用数字指定;即;Jan=01、Feb=02 等),我需要一种方法来将迄今为止的所有信息相加。 我使用的公式如下: =SUMPRODUCT(SUMIFS(INDEX('Program Data'!A:GA,0,MATCH("*"&AC7&" "&AC10&" -"&A8&" "&B15&"*", 'Program Data'!1:1,0)),'Program Data'!$M:$M,$A2,'Program Data'!$E:$E,B2:B6)) 其中 A8 是包含所选感兴趣月份的单元格。 所以让我们说 A8=08(八月)。 我想对 A8 的所有先前值执行此公式并将它们加在一起,向上添加直到A8 等于当前选择。 (或者虽然A8<09,但是这可以实现) 因此假设 A8=08,我希望它从 A8=01 开始,运行此代码并找到该值。 然后运行 ​​A8=02,找到那个值,然后把它加到第一个值上,继续这个直到我们得到 A8=08,然后报告最终值。 由于单位数月份是用两位数写的(即;01、02、03 等)并使用前导 0 进行搜索,我认为循环帐户对它相当重要,直到我们得到两位数月份(10 , 11, 12).... 类似于(这不是代码
  • Return the count of the most frequently occurring string based on multiple criteria
    I have a large table of data in Excel 2013 similar to the following: A(Include?) B(Name) C(Region) 1 John Doe North 0 Jim Stevens South 0 Tom Brooks West 1 Bob Smith West 1 Mike Thomas North 0 Steve Adams East 1 John Smith Northwest I need help coming up with an Excel formula that will show me the count of the most frequently occurring Region name, but only if the corresponding Include? value is equal to 1. In this example, the formula that I need would output the number 2, because North is the most frequently occurring entry in the Region column, while only counting the entries whose Include
  • Combine rows of a table with similar values using VBA Excel
    I have to do sumif multiple time to get this problem solved. Problem: I have a table with Part Number as the first column and it is always the same for unique part. However, the description 1 and description 2 are not perfect, even for the same part number (due to the typo, etc). I need to combine the Quantity at different inventories: QTY AT V1, V2, and V3 The result if I use multiple sumif and choose the the description appears first. Of course, there are like 50k of rows with many different PN#. Error often occurs if using sumif due to human error. I would like to ask for help on this one
  • Selecting a Specific Column of a Named Range for the SUMIF Function
    I am trying to create a SUMIF function that dynamically adds up values in a specific column of a named range in my Excel sheet. It is very easy to do this when there is no named range : The formula picks out all the cells that contain "London" in their name and sums up the expenses related to London. What I am trying to do is to use a named range called TripsData (A2:B5) and tell the SUMIF function to sum the entries in the column 2 of this range that meet the criterion of having London in their name. How can I make this work without needing to create a second named range for column 2 and
  • 使用 VBA Excel 合并表中具有相似值的行(Combine rows of a table with similar values using VBA Excel)
    问题 我必须多次执行 sumif 才能解决此问题。 问题:我有一个表,第一列是部件号,对于唯一部件它总是相同的。 然而,描述 1 和描述 2 并不完美,即使是相同的部件号(由于拼写错误等)。 我需要结合不同库存的数量:V1、V2 和 V3 的数量 如果我使用多个 sumif 并选择描述,结果首先出现。 当然,大约有 50k 行具有许多不同的 PN#。 如果由于人为错误使用 sumif,则经常会发生错误。 我想在这方面寻求帮助。 比较 PN#,如果它们在不同库存位置的数量总和相同,则描述 1 和 2 仅选取最先出现的内容(如 CAR - BLACK 和 4 WHEELS)。 有一些类似的问题和答案。 但是,它们不能很好地工作。 合并单元格 回答1 这应该可以解决您的问题。 我设置了一个带有 2 个选项卡的工作簿:RawData 和 PNTotals。 我创建的数据类似于您示例中的两行。 我有 26 行,有 3 个不同的 PN#:本田、丰田和起亚。 无论您有多少行和 PN#,该代码都有效。 运行下面的代码后,我最终在 PNTotals 选项卡上按 PN 得到总计,如下所示: HONDA CAR - BLACK 4 WHEELS 936 516 2214 TOYOTA CAR 864 414 2079 KIA CAR - RED SPORT PACKAGE 504 204 1234
  • 需要Excel SUMIF公式(Excel SUMIF formula needed)
    问题 我正在寻找一个 SUMIF 公式,如果该行满足特定条件,该公式会将列的所有值相加。 标准是它需要匹配 3 个类别(A、B 或 C)中的任何一个。 我的数据如下所示: A 5 4 B 32 4 B 12 6 B 4 7 A 21 342 C 2 2 C 1 1 A 3 5 A <total A> <total A> etc B <total B> <total B> etc C <total C> <total C> etc 该公式在水平拖动时应该有效。 在此先感谢您的帮助! 回答1 在 B 列中 =SUMIF($A$1:$A$8,"=A",B$1:B$8) =SUMIF($A$1:$A$8,"=B",B$1:B$8) =SUMIF($A$1:$A$8,"=C",B$1:B$8) 在 C 列中 =SUMIF($A$1:$A$8,"=A",C$1:C$8) =SUMIF($A$1:$A$8,"=B",C$1:C$8) =SUMIF($A$1:$A$8,"=C",C$1:C$8) 回答2 @Mike Meinz 给了你正确的答案。 公式可以简化如下: 在 B 列中,标准 A、B 和 C 的总计可计算如下: =SUMIF($A1:$A8,"A",$B1:$B8) =SUMIF($A1:$A8,"B",$B1:$B8) =SUMIF($A1:$A8,"C",$B1:$B8) 注意
  • Excel VBA-在一个单元格中合并具有重复值的行,并在另一单元格中合并值(Excel VBA - Combine rows with duplicate values in one cell and merge values in other cell)
    问题 我正在尝试在一列中找到重复的值,并将第二列的值合并为一行。 我也想对第三列中的值求和。 例如: A B C D h 4 w 3 h 4 u 5 h 4 g 7 h 4 f 4 k 9 t 6 k 9 o 6 k 9 p 9 k 9 j 1 会成为 A B C D k 9 t;o;p;j 22 h 4 w;u;g;f 19 我在第一部分中一直使用的代码是 Sub mergeCategoryValues() Dim lngRow As Long With ActiveSheet lngRow = .Cells(65536, 1).End(xlUp).Row .Cells(1).CurrentRegion.Sort key1:=.Cells(1), Header:=xlYes Do If .Cells(lngRow, 9) = .Cells(lngRow + 1, 9) Then .Cells(lngRow, 11) = .Cells(lngRow, 8) & "; " & .Cells(lngRow + 1, 8) .Rows(lngRow +1).Delete End If lngRow = lngRow - 1 Loop Until lngRow < 2 End With End Sub (请原谅缩进) 我遇到的问题是它将找到第一对重复项,但不是全部。
  • Trouble creating an excel Do Loop
    I have data that I need to create some sort of loop for. Basically, I'm searching for a column that has the info I need using a string search, then summing based on various criteria. My problem is that the data is separated by months (using a numberical designation; i.e; Jan=01, Feb=02, etc) and I need a way to add up all the info to date. The formula I am using is below: =SUMPRODUCT(SUMIFS(INDEX('Program Data'!A:GA,0,MATCH("*"&AC7&" "&AC10&" -"&A8&" "&B15&"*", 'Program Data'!1:1,0)),'Program Data'!$M:$M,$A2,'Program Data'!$E:$E,B2:B6)) Where A8 is the cell that contains the selected month of
  • 根据多个条件返回最常出现的字符串的计数(Return the count of the most frequently occurring string based on multiple criteria)
    问题 我在 Excel 2013 中有一个大型数据表,类似于以下内容: A(Include?) B(Name) C(Region) 1 John Doe North 0 Jim Stevens South 0 Tom Brooks West 1 Bob Smith West 1 Mike Thomas North 0 Steve Adams East 1 John Smith Northwest 我需要帮助想出一个 Excel 公式,该公式将显示最常出现的区域名称的计数,但前提是相应的包括? 值等于 1。 在此示例中,我需要的公式将输出数字 2,因为 North 是Region列中出现频率最高的条目,而仅计算其Include? 值等于 1。 我已经能够使用以下数组函数找到最常出现的区域(不考虑包含列): =INDEX(C:C,MODE(IF(C:C<>"",MATCH(C:C,C:C,0)))) 然后我能够使用以下方法找到该值出现在列中的次数: =COUNTIF(C:C,INDEX(C:C,MODE(IF(C:C<>"",MATCH(C:C,C:C,0))))) 但是,当我尝试考虑Include? 专栏,我没有得到正确的答案。 我最好的猜测是: =COUNTIFS(C:C,C:C,INDEX(C:C,MODE(IF(C:C<>"",MATCH(C:C,C:C,0)))),A:A,1
  • SUMIFS source code for excel 2003 AND the ability to calculate on columns
    we are looking to find the original? source code for SUMIFS to use in out excel sheet (for both 2003 and 2007. Here is why: 2003 doest support the SUMIFS method When we do have SUMIFS we cannot utilize formulas "around" the columns (like YEAR()) For example, we want to calculate the ANSWERS that match the YEAR value of the date in cell A1 with the date values in range L:L. Now this doesnt work because we cant use YEAR(L:L) and hence we need to make another column M:M with the YEAR values from L:L Thus we need the source to be able to upgrade the code further =SUMIFS(ANSWERS;L:L;"="&YEAR(A1)) <