如果另一列中的单元格包含特定文本则求和
Author: Xiaoyang Last Modified: 2025-05-07
本教程将向您展示如何在另一列中包含特定或部分文本时对值进行求和。以下面的数据范围为例,要获取包含“T恤”文本的产品的总数量,SUMIF 函数和 SUMPRODUCT 函数都可以在 Excel 中完成此任务。
如果单元格包含特定或部分文本,则使用 SUMIF 函数求和
如果另一列中的单元格包含特定文本,您可以使用带有通配符(*)的 SUMIF 函数来求和,通用语法如下:
硬编码文本的通用公式:
=SUMIF(范围,"*文本*",求和范围)
- 范围:您希望根据条件评估的数据范围;
- *文本*:您希望基于其求和的条件。这里,* 通配符用于查找任意数量的字符,要匹配所有包含特定文本的项,请将文本放在两个 * 字符之间。(请注意,您必须用双引号括起文本和通配符。)
- 求和范围:您希望求和的具有匹配数值的单元格范围。
带单元格引用的通用公式:
=SUMIF(范围,"*"&单元格&"*",求和范围)
- 范围:您希望根据条件评估的数据范围;
- "*"&单元格&"*":您希望基于其求和的条件;
- *:通配符,用于查找任意数量的字符。
- 单元格:包含要查找的特定文本的单元格。
- &:此连接运算符 (&) 用于将单元格引用与星号 (*) 连接起来。
- 求和范围:您希望求和的具有匹配数值的单元格范围。
了解函数的基本操作后,请根据需要使用以下任一公式,然后按 Enter 键获取结果:
=SUMIF($A$2:$A$12,"*T恤*",$B$2:$B$12)(手动输入条件)=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)(使用单元格引用)
注意:此 SUMIF 函数不区分大小写。
如果单元格包含特定或部分文本,则使用 SUMPRODUCT 函数求和
SUMPRODUCT 函数也可以帮助您完成此任务,而无需使用任何通配符。在这里,您应该将 ISNUMBER 和 SEARCH 函数嵌套在 SUMPRODUCT 函数内,通用语法如下:
=SUMPRODUCT(求和范围*(ISNUMBER(SEARCH(条件,范围))))
- 求和范围:您希望求和的具有匹配数值的单元格范围;
- 条件:您希望基于其求和的条件。它可以是单元格引用或您定义的特定文本;
- 范围:您希望根据条件评估的数据范围;
请在空白单元格中使用以下任一公式,然后按 Enter 键返回结果:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T恤",$A$2:$A$12)))) (手动输入条件)=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))(使用单元格引用)
此公式的解释:
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T恤",$A$2:$A$12))))
- SEARCH("T恤",$A$2:$A$12):SEARCH 函数从数据范围 A2:A12 返回特定文本“T恤”的位置,因此,您将得到如下数组:{5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}。
- ISNUMBER(SEARCH("T恤",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}):此 ISNUMBER 函数用于测试数值,并返回一个新数组:{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}。
- $B$2:$B$12*(ISNUMBER(SEARCH("T恤",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}:将这两个数组相乘,数学运算会将 TRUE 和 FALSE 值强制转换为 1 和 0。因此,两个数组的乘积将如下所示:{347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}。
- SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T恤",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}):最后,SUMPRODUCT 函数对数组中的所有值求和以获得结果:1605。
相关函数:
- SUMIF:
- SUMIF 函数可以根据一个条件对单元格求和。
- SUMPRODUCT:
- SUMPRODUCT 函数可用于将两列或多列或数组相乘,然后得到乘积的总和。
- ISNUMBER:
- Excel 的 ISNUMBER 函数当单元格包含数字时返回 TRUE,否则返回 FALSE。
- SEARCH:
- SEARCH 函数可以帮助您从给定的文本字符串中找到特定字符或子字符串的位置。
更多文章:
- 如果单元格以特定文本或字符开头或结尾则求和
- 要对以特定值开头或结尾的对应单元格的值求和,您可以应用带有通配符 (*) 的 SUMIF 函数来实现。本文将详细介绍如何使用该公式。
- 求最小或底部 N 个值的和
- 在 Excel 中,使用 SUM 函数对一系列单元格求和非常容易。有时,您可能需要对数据范围内的最小或底部 3、5 或 n 个数字求和,如下面的截图所示。在这种情况下,SUMPRODUCT 与 SMALL 函数结合可以帮助您在 Excel 中解决这个问题。
- 根据条件求最小或底部 N 个值的和
- 在之前的教程中,我们讨论了如何对数据范围内的最小 n 个值求和。在本文中,我们将执行更高级的操作——根据一个或多个条件对最低的 n 个值求和。
最佳的办公生产力工具
Kutools for Excel - 助您脱颖而出
🤖 | Kutools AI 助手:基于以下功能彻底改变数据分析方式:智能执行 | 生成代码 | 创建自定义公式 | 分析数据并生成图表 | 调用Kutools函数… |
热门功能: 查找、高亮或标记重复项 | 删除空行 | 合并列或单元格而不丢失数据 | 四舍五入无需公式 ... | |
超级VLookup: 多条件 | 多值 | 跨多个工作表 | 模糊查找... | |
高级下拉列表: 简易下拉列表 | 级联下拉列表 | 多选下拉列表... | |
列管理器: 添加指定数量的列 | 移动列 | 切换隐藏列的可见状态 | 比较列以 选择相同和不同的单元格 ... | |
精选功能:网格聚焦 | 设计视图 | 增强编辑栏 | 工作簿与工作表管理器 | 资源库(自动文本) | 日期提取 | 合并数据 | 加密/解密单元格 | 按列表发送电子邮件 | 超级筛选 | 特殊筛选(筛选粗体/斜体/删除线...) ... | |
排名前15的工具集: 12个 文本 工具(添加文本,删除特定字符 ...) | 50多种 图表 类型(甘特图 ...) | 40多种实用 公式(基于生日计算年龄 ...) | 19个 插入 工具(插入二维码,从路径插入图片 ...) | 12个 转换 工具(小写金额转大写,汇率转换 ...) | 7个 合并与拆分 工具(高级合并行,分割Excel单元格 ...) | ... 还有更多 |
Kutools for Excel拥有超过300种功能,确保您需要的功能只需一键即可实现...
Office Tab - 在Microsoft Office(包括Excel)中启用标签式阅读和编辑
- 一秒内切换数十个打开的文档!
- 每天为您减少数百次鼠标点击,告别鼠标手。
- 在查看和编辑多个文档时,您的工作效率将提高50%。
- 为Office(包括Excel)带来高效的标签页,就像Chrome、Edge和Firefox一样。