Excel数组公式避坑：用加减乘除替代AND/OR，底层逻辑与实战

有一次，一位做运营的朋友发给我一个Excel问题。他的公式长这样：=SUMPRODUCT(IF(AND(A2:A100="华东",B2:B100="A类"),C2:C100,0))，按下三键结束，结果返回#VALUE!。他问我是不是哪里写错了。我只看了一眼就说：问题出在AND上。这不是笔误，而是很多Excel老手都会踩的一个坑——你以为AND能帮你完成数组里的逐行判断，实际上它把所有条件“打包”成一个值返回了。后来我建议他把写法换成=SUMPRODUCT((A2:A100="华东")*(B2:B100="A类")*C2:C100)，问题立刻解决。

从那之后，我在处理数组逻辑运算时几乎不再直接用AND和OR，而是用“加减乘除”来替代。这不是炫技，而是数组公式的底层逻辑决定的。今天这篇就把这套替代逻辑、应用场景、坑点和实战案例一次讲透。

1. 数组公式里AND和OR为什么“失灵”——一次排查引发的思考

1.1 一次真实的公式“翻车”现场

先还原一下我朋友那张表的场景。他有一份销售流水，大概长这样：

• A列：销售区域（华东、华北、华南）
• B列：产品类别（A类、B类、C类）
• C列：销售金额

他想统计“华东区域A类产品的销售总额”。这需求太常见了，正常人第一反应用SUMIFS就能写：

=SUMIFS(C2:C100,A2:A100,"华东",B2:B100,"A类")

但他偏不，他用了前面那个数组公式。为什么会想到IF(AND(...))套SUMPRODUCT？因为很多教程里教过一句“多条件求和用SUMPRODUCT配合数组判断”，于是他就参考了一个普通的非数组逻辑写法，把AND硬塞了进去。结果就是#VALUE!。

这个错误不是个别现象。在很多Excel交流群里，只要有人把AND或OR放进数组公式里，十有八九会出问题。根源在于对AND函数工作机制的误解。

1.2 AND和OR只关心“整体”，不关心“每一行”

AND函数的逻辑是“所有参数都为TRUE，则返回TRUE，否则返回FALSE”。当你把两个数组传给它时，它不会逐个数组元素地去配对判断，而是先把每个数组内部的所有元素做一次“整体评估”——只要数组里有一个FALSE，整个数组就被视为FALSE——然后返回一个单一的TRUE或FALSE。

举个例子：

=AND({TRUE;TRUE;FALSE},{TRUE;TRUE;TRUE})

结果是什么？FALSE。因为第一个数组里有FALSE。它不可能返回{TRUE;TRUE;FALSE}这样逐行对应的数组。OR同理，它只看“有没有任何一个TRUE”。

这种设计在普通单元格计算里完全没问题，但在数组公式里就麻烦了。数组公式的核心理念是什么？逐元素运算。你要的是A1和B1比、A2和B2比、A3和B3比……然后各自得到一个结果。可AND和OR直接把整个数组“压实”成了一个值，后续的乘法、IF判断全部跟着错乱，结果自然不对。

1.3 那为什么加减乘除在数组公式里就好用？

因为加减乘除是逐元素运算。你把(A2:A100="华东")这个比较表达式放进公式里，Excel会返回一个数组，里面是TRUE和FALSE的集合。对这个数组去做乘法、加法、减法，Excel会逐行处理：第一个TRUE和第一个TRUE相乘，第二个FALSE和第二个TRUE相乘，一路推进。这个过程完全符合数组公式的“逐行逐元素”预期。

这就是标题里说的“数组革命”——用算术运算替代逻辑函数，本质上是在遵循数组公式的原生工作方式，而不是和它拧着来。

2. 加减乘除怎么“翻译”布尔逻辑——一张映射表讲透原理

2.1 TRUE和FALSE在算术里的真实身份

先得说清楚一个基础：在Excel里，TRUE参与算术运算时会被自动当成1，FALSE会被当成0。这不是某个函数的行为，而是Excel的通用规则。你可以随手在任意单元格输入=TRUE+1，结果是2；输入=FALSE*100，结果是0。

这个规则是整个算术替代方案的根基。因为一旦比较表达式返回布尔值数组，你就能对这个数组做任何数学运算，而运算结果依然保留着“逻辑判断”的信息。

我用一张映射表总结一下：

为什么要重新强调这张表？因为很多人在网上看过“用乘号替代AND、加号替代OR”的说法，但不知道为什么替代，更不知道替代后返回值可能不是0和1，而是2、3这种“超预期值”。搞懂原理之后，你才能应对各种意外情况。

2.2 乘法为什么是“且”

(条件1)*(条件2)的本质是0和1的乘法。只有当两个条件都为1时，乘积才是1；只要有一个为0，结果就是0。这和AND的判定完全一致。

这个操作在数组公式里的优势非常明显。比如你要统计“华东区域A类产品的订单数”：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A类"))

Excel会先分别计算出两个布尔数组，然后把它们逐行相乘。A2是“华东”、B2是“A类”，这一行就返回1；A3是“华东”、B3是“B类”，这一行就返回0。最后SUMPRODUCT把这一列0和1加起来，就是符合条件的行数。

2.3 加法为什么是“或”，但有个隐藏问题

(条件1)+(条件2)也遵循0和1的加法。两个条件中有一个为1，结果就是1；两个都为1，结果就是2。

这里就引出一个关键问题：如果你需要的是“满足任一条件即为TRUE”的逻辑判定，那么2和1在数学上都表示“满足”，可当你直接把加法结果放进下一步运算时，2会成为一个意外值。

举个例子。统计“销售员是小张或小李”的订单数：

=SUMPRODUCT((A2:A100="小张")+(A2:A100="小李"))

注意，一个人不可能同时等于“小张”又等于“小李”，所以每行最多只有一个条件成立，结果要么是0要么是1，不会出现2。这种场景下加法完全可以放心用。

但如果你的两个条件不是互斥的，比如统计“A列是华东或B列是A类”的订单数，同一行里两个条件可能同时成立，加法就会返回2。此时直接求和会把这一行算两次。要得到“行数”而不是“条件成立次数”，就得给加法结果套一层SIGN或>0判断：

=SUMPRODUCT(SIGN((A2:A100="华东")+(B2:B100="A类")))

或者用((A2:A100="华东")+(B2:B100="A类")>0)的方式强制把2变成TRUE，再参与后续运算时就会自动变成1。

2.4 减法：用来做“非”和“排除”

减法在逻辑运算里用得不算多，但它表达“非”特别直观。1-条件就是条件的反向判断：条件为TRUE时结果为0，条件为FALSE时结果为1。

它最常见的应用场景是“排除某个类别”。统计“非退货类订单”的数量：

=SUMPRODUCT(1-(B2:B100="退货"))

这里(B2:B100="退货")返回0和1的数组，1-把它们反过来，退货的变成0，非退货的变成1，加起来正好是非退货订单数。

更复杂一点的场景是“满足A但不满足B”：

=SUMPRODUCT((A2:A100="华东")*(1-(B2:B100="退货")))

这比写(A2:A100="华东")*((B2:B100)<>"退货")看起来绕，但在某些文本比较场景下，“排除等于某个值”的逻辑用减法不容易出错，尤其是当你需要处理多个排除条件时，1-(条件1)-(条件2)的写法比<>云集的方式更清爽。

2.5 除法去哪了？

加法对应OR，乘法对应AND，减法对应NOT，那除法呢？严格来说，除法也能表达“全部满足”的逻辑，因为1除以1等于1，而其他任何组合都会得到0或小数。但除法的致命弱点是遇到0时会返回#DIV/0!错误，所以极少有人用它做逻辑运算。

我见过有人用1/((条件1)+(条件2))来做“两个条件至少一个满足”的判断，因为只有分母为1时结果才是1，分母为0或2时会得到错误或0.5。这种写法风险太大，我强烈不建议在正式报表里用。记住乘、加、减三件套就够了，除法留给真正的数值计算。

2.6 优先级是最大陷阱：宁可多加括号

小学数学告诉我们，乘法优先于加法。这个规则在Excel里同样生效。所以当你写(条件1)+(条件2)*(条件3)时，Excel会先算(条件2)*(条件3)，再把结果和(条件1)相加，这很可能不是你想要的逻辑。

正确做法是把每一个独立条件都用括号包起来，再用运算符号连接：

((条件1)+(条件2))*(条件3)

括号多写几层不会出错，少写一层就可能是完全不同的统计结果。我见过太多人因为省括号，把“华东且（小张或小李）”统计成了“（华东且小张）或小李”，数字差十万八千里。后面实战部分会再强调一次。

3. 五个真实场景直接抄作业：多条件计数、求和与区间判断

3.1 场景一：多条件计数

这是替代COUNTIFS最经典的场景。我有一份员工销售表，A列是区域，B列是产品线，要统计“华东区域A产品线的记录条数”。

普通公式：

=COUNTIFS(A2:A100,"华东",B2:B100,"A产品线")

数组替代公式：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A产品线"))

为什么放着COUNTIFS不用要绕一圈？因为COUNTIFS的参数区域必须是单元格引用，条件也必须是常量或者引用某个单元格。当你的条件本身是个数组，比如你要同时判断多个区域，或者要和FILTER等动态数组函数配套使用时，COUNTIFS就力不从心了。算术替代方案唯一的“硬需求”是：条件表达式的长度要和数据区域一致，否则会返回#N/A。

如果条件来自单元格，也没问题：

=SUMPRODUCT((A2:A100=C1)*(B2:B100=D1))

这样修改条件时直接改单元格，公式不用动，比在COUNTIFS里改引用的体验好不少。

3.2 场景二：单列多值匹配（OR条件）

统计“销售员是小张或小李”的订单数。这是单列内OR条件的典型场景。

=SUMPRODUCT(((A2:A100="小张")+(A2:A100="小李"))>0)

注意这里的写法。前面讲过，一个人不可能同时等于“小张”和“小李”，所以不加>0也能得到正确的结果：

=SUMPRODUCT((A2:A100="小张")+(A2:A100="小李"))

但为了防止出现意外，比如两个条件本身有重叠，更稳妥的写法是外面套>0或SIGN。我个人的建议是：如果你明确知道条件之间互斥，可以省略；否则一律加上>0，把结果限定在0和1之间。

如果你的OR条件扩展到了三个、四个，公式长度会急剧膨胀。这时你可以换个思路，用MATCH判断“值是否存在于列表”：

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A100,{"小张","小李","小王"},0)))

这个写法后面在新增人员时，只需要维护常量数组，公式结构完全不变。它和加法OR的区别在于，MATCH天然返回位置或错误，ISNUMBER把结果转换成TRUE/FALSE，--再转成1/0。这套组合在数据验证、报表自动化里非常实用。

3.3 场景三：混合条件，AND和OR嵌套

最常见的多条件统计，比如“华东区域，且销售员是小张或小李”的订单数。这里既要AND也要OR，算术写法可以一步到位：

=SUMPRODUCT((A2:A100="华东")*((B2:B100="小张")+(B2:B100="小李")))

(A2:A100="华东")负责AND部分，((B2:B100="小张")+(B2:B100="小李"))负责OR部分。因为“小张”和“小李”互斥，这里不需要再套>0。整个式子读起来就是“华东乘以（小张或小李）”，非常直观。

如果你想要“华东或华北，且是A类产品”，就写成：

=SUMPRODUCT(((A2:A100="华东")+(A2:A100="华北"))*(B2:B100="A类"))

注意这里为什么不套>0？因为同一个单元格不可能既等于“华东”又等于“华北”，两个条件互斥，加法只会得到0或1。但为了保险起见，我还是建议在最外层套一层>0，把结果固定成明确的布尔值：

=SUMPRODUCT(((A2:A100="华东")+(A2:A100="华北")>0)*(B2:B100="A类"))

这种写法虽然字符多了一点，但逻辑上无懈可击，也不会因为后续改动条件而产生“2”的隐患。

3.4 场景四：多条件求和

把计数的乘号逻辑套上求和区域，就变成了多条件求和。统计“华东区域A类产品的销售额”：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A类")*C2:C100)

注意，C2:C100是求和区域，不需要加任何比较、也不用包括号，直接乘进去。它和前两个条件数组逐行相乘，符合条件的那一行会得到销售额本身，不符合的得到0，SUMPRODUCT最后把所有乘积相加。

这里有个小细节：如果求和区域里有文本，乘法会把文本当0处理吗？不会。文本参与乘法会直接报#VALUE!。所以求和区域里不能有文本型单元格，哪怕只有一个也不行。遇到这种情况，要么数据清洗，要么改用SUMIFS处理。

如果需求变成了“华东或华北区域的销售额”，注意括号层级：

=SUMPRODUCT(((A2:A100="华东")+(A2:A100="华北")>0)*C2:C100)

减法排除类场景，统计“非退货状态下的华东区销售额”：

=SUMPRODUCT((A2:A100="华东")*(1-(B2:B100="退货"))*C2:C100)

这套组合可以覆盖绝大多数多条件求和需求。

3.5 场景五：区间判断

统计“销售额在1000到3000之间”的订单数。这种需求通常可以用COUNTIFS的“大于等于”加“小于等于”两个条件搞定，但数组写法更灵活：

=SUMPRODUCT((C2:C100>=1000)*(C2:C100<=3000))

注意边界条件的方向。如果你要的是“70到80之间（含70和80）”，公式是：

=SUMPRODUCT((C2:C100>=70)*(C2:C100<=80))

如果是不含边界，就把>=改成>，把<=改成<。这里有读者容易把“70~80”写成(C2:C100>=70)*(C2:C100<80)，结果差了80分那一档的人数，事后怎么对都对不上。区间判断一定先确认“含不含端点”，再决定用哪个比较运算符。

区间判断还有一个变体：判断日期是否在某个月份内。比如统计2024年5月的订单数：

=SUMPRODUCT((TEXT(A2:A100,"YYYY-MM")="2024-05")*1)

这里TEXT会生成一个文本数组，和“2024-05”比较后返回TRUE/FALSE数组，*1或者前面的*都能把它转成1/0参与求和。如果你用的是Excel 365，更推荐BYROW加TEXT的组合，但SUMPRODUCT这种写法在各种版本里都通用。

3.6 动态数组新函数下的“算术革命”

如果你用的是Excel 365或WPS最新版本，FILTER函数让数组逻辑变得更直观。比如筛选出华东区域的A类产品：

=FILTER(A2:C100,(A2:A100="华东")*(B2:B100="A类"))

FILTER的第二个参数就是“包含哪些行”的判断数组，1保留、0剔除。这里的*完全是前面讲的AND替代逻辑。再用加法表达OR：

=FILTER(A2:C100,((A2:A100="华东")+(A2:A100="华北")>0)*(B2:B100="A类"))

所以这套“加减乘除替代逻辑函数”的思路不仅仅是SUMPRODUCT时代的老古董，它在新的动态数组函数里同样是核心语法。理解透了，你写FILTER、SORT、UNIQUE配套的条件判断都会顺手很多。

4. 边界情况与隐蔽坑点：公式算不出来未必是逻辑错

4.1 空单元格参与比较的坑

A2:A100里如果有些单元格是真空的，那么A2:A100=""这个判断对空单元格会返回TRUE。这听起来没毛病，但如果你要统计“有填写的订单数”，写：

=SUMPRODUCT((A2:A100<>"")*1)

这个公式会把空单元格排除，正确。但如果你把条件和求和区域都放在一起，某个区域有空单元格，乘法会把空单元格当成0处理，导致求和结果比真实值小。比如：

=SUMPRODUCT((A2:A100="华东")*(B2:B100="A类")*C2:C100)

如果C列有某个空单元格，它会作为0参与求和。而如果用SUMIFS处理同样的数据，空单元格会被忽略，结果可能会不一样。这不是公式逻辑错误，而是数据处理口径问题。我的建议是：做数组运算前，先用筛选或COUNTA确认数据区域里没有真空单元格，避免结果的不可预期。

4.2 文本型数字和真数字的混用

从系统导出的表格经常出现“文本型数字”——单元格左上角有个绿色小三角。文本“1000”和数字1000在比较时，C2:C100>=1000这种写法会把文本“1000”自动转换成数字参与比较，问题不大。但当你用文本型数字做乘法时：

=SUMPRODUCT((A2:A100="华东")*C2:C100)

C列里如果有文本型数字，Excel在乘法运算时通常能做隐式转换，结果一般没问题。真正容易出乱子的是VLOOKUP或MATCH查找时，文本和数字不匹配。所以在用算术替代方案之前，建议先用=ISNUMBER(C2)检查数据类型，或者用--C2:C100把文本强制转成数值。

4.3 错误值的“传染”效应

算术运算的另一个特点是“一个错误值毁掉整个公式”。如果C2:C100里有一个单元格是#N/A或#DIV/0!，那么乘法结果里这一行就是错误值，SUMPRODUCT直接返回错误。而SUMIF和SUMIFS会自动忽略错误值，这是它们的一大优势。

所以当你的数据源可能存在查找错误时，需要先包一层IFERROR：

=SUMPRODUCT((A2:A100="华东")*IFERROR(C2:C100,0))

注意，某些版本里IFERROR在数组公式里需要三键确认。如果你不想用数组公式，就用SUMIFS兜底，这是它仍然不可替代的理由之一。

4.4 优先级和括号问题：再小心都不为过

前面说过，写成(A条件)+(B条件)*(C条件)会让Excel先算(B条件)*(C条件)再和(A条件)相加，这个顺序可能完全偏离你的意图。比如你要统计“华东区域且（小张或小李）”：

=SUMPRODUCT((A2:A100="华东")+(B2:B100="小张")*(C2:C100="小李"))

这个公式逻辑上已经错了。它会“华东加（小张且小李）”混在一起。正确写法是：

=SUMPRODUCT((A2:A100="华东")*((B2:B100="小张")+(C2:C100="小李")))

关于括号，我有一条经验：凡是逻辑组合里出现加法，就把加法部分整体括起来；凡是可能出现多个运算符号混排，就全部括起来。公式长了无所谓，结果对才是硬道理。

4.5 为什么要加--而不是乘1

在实际案例里经常看到--这个符号，它叫“双减号”，作用是强制把TRUE/FALSE转换成1/0。比如：

=SUMPRODUCT(--(A2:A100="华东"))

这里的--相当于*1，但字符更短。很多人不理解为啥要两个减号，第一个减号把TRUE变成-1，第二个减号把-1又变回1。两个负号相邻，顺序执行。单个减号不会报错，但会把TRUE变成-1，导致结果变成负数，所以必须是双减号。

在SUMPRODUCT里，如果你直接写=SUMPRODUCT((A2:A100="华东"))，某些版本会提示参数不对或返回0，因为SUMPRODUCT默认期望接收数值数组。给它套一个--或*1就好了。不过如果你用SUMPRODUCT的乘法逻辑，比如(条件1)*(条件2)，由于乘法本身会把布尔值转成数值，通常不用额外加--。只有当前面只有单个布尔数组时，--才是必需品。

4.6 使用F9调试数组公式

数组公式出错时，最快的诊断方式是选中公式里的某个片段，按F9查看这一段的计算结果。比如你怀疑(A2:A100="华东")有问题，可以只选中这部分，按F9，Excel会显示一串{TRUE;FALSE;TRUE...}。如果能看懂这个结果，公式的问题基本就能定位。

需要注意的是，F9调试后一定要按Esc退出，不要按回车，否则公式会被替换成计算结果，造成无法恢复的修改。这是每个想深入研究Excel数组运算的人必须养成的习惯。

4.7 版本差异：传统数组公式和动态数组

Excel 365和Excel 2021支持动态数组，公式不用按Ctrl+Shift+Enter，结果会自动溢出到相邻单元格。而传统Excel（包括WPS的某些模式）需要Ctrl+Shift+Enter确认数组公式，大括号{}会包裹整个公式。

用SUMPRODUCT的好处是它本身就是“数组计算”但不需要三键确认，所以这篇文章里的写法在各版本里都通用。如果你用的是FILTER这类新函数，就必须要动态数组环境的支持。建议先在Excel版本较新的机器上开发，再考虑向下兼容的问题。

5. 我用了三年的一点体会：什么时候该用算术替代

5.1 不要为了替代而替代

如果你只是想在单表里做一个简单的多条件计数或求和，COUNTIFS、SUMIFS依然是首选。它们语法清晰、可读性强、能自动忽略错误值，而且不用考虑布尔数组的转换问题。算术替代方案的价值主要在三种场景里才会凸显：

• 条件本身需要动态计算，不能写成固定常量
• 条件来自另一个数组区域，比如用MATCH、VLOOKUP动态生成条件列表
• 需要和动态数组函数（FILTER、SORT、UNIQUE）复合使用

举个例子，统计“最近30天有购买记录的会员数”，如果条件区域本身是动态的，COUNTIFS写起来会非常别扭：

=SUMPRODUCT((A2:A100>=TODAY()-30)*(B2:B100="已支付"))

这里TODAY()-30是动态条件，算术方案可以无缝衔接；如果用COUNTIFS，需要先在一个辅助单元格里算出TODAY()-30，公式才能引用，多一步操作就多一分出错概率。

5.2 用LET提升可读性

公式越长，越难以维护。Excel 365和WPS里的LET函数允许你给中间结果命名。比如这个复杂公式：

=SUMPRODUCT((A2:A100="华东")*((B2:B100="小张")+(B2:B100="小李")>0)*C2:C100)

用LET重写以后清晰很多：

=LET( 区域判断, A2:A100="华东", 销售判断, (B2:B100="小张")+(B2:B100="小李")>0, 销售额, C2:C100, SUMPRODUCT(区域判断*销售判断*销售额) )

这样每个条件的含义一目了然，回头改条件也只需改动一处。日常写复杂报表时，我强烈建议多用LET，不然半年后再看自己写的长公式，真的会怀疑这是不是自己写的。

5.3 性能优化的实际问题

数组运算虽然灵活，但也有性能成本。如果数据量到了几万行，SUMPRODUCT叠加多个条件数组，速度会明显变慢。这时候有几个优化方向：

• 尽量缩小引用范围，避免A:A这种整列引用，精确到A2:A20000能让计算量大幅下降
• 用LET把反复计算的条件数组存成变量，避免每行重复计算
• 如果数据量超过10万行，建议先用“表格”功能加上结构化引用，或者考虑用Power Query做数据清洗后再返回Excel
• Excel 365用户可以考虑BYROW配合LAMBDA，有时候性能比嵌套数组更好

5.4 这套思路的真正意义

我见过很多Excel学习者卡在“记不住公式”这个阶段，总觉得会写的公式越多越厉害。但实际工作中真正重要的不是记住多少个函数，而是理解Excel的运算逻辑。当你吃透了“布尔值可以参与算术运算”这个原理，你会发现SUMPRODUCT、FILTER、COUNTIFS、SUMIFS这些看似无关的函数都能被一条线索串起来。

回到文章开头的那个问题，我朋友后来不只解决了那次统计难题，还学会了一个通用技巧：以后只要遇到“多个条件需要同时满足”，他第一反应不是找“有没有多条件函数”，而是先把条件拆成布尔数组，再决定用*还是+连接。这种思维方式的转变，才是这次“数组革命”给他的最大收获。

根据我个人的实操经验，Excel里最值得花时间掌握的其实不是某个冷门函数，而是“数组思维”。一旦你习惯了用布尔数组和算术运算符去表达条件逻辑，复杂报表里的很多统计需求都会变得异常丝滑。希望这篇帖子能帮你跨过AND和OR那个坎，在Excel里真正体会到数组公式的威力。