Excel中能否在公式中加入变量数量以简化库存统计SUM IF公式？

核心思路 #

通过提取单元格中的零件编号和对应数量，用批量逻辑替代逐个数量的IF判断，适配1到30的所有数量场景。

公式实现（Excel 365/2021及以上版本） #

假设目标零件编号存于单元格A2（单独存放方便批量复用），扫码数据区域为N1:N106，使用以下公式即可完成统计：

=SUMPRODUCT(
  --(TEXTBEFORE(N1:N106, " *",, TRUE)=A2),
  IFERROR(--TEXTAFTER(N1:N106, " *",, TRUE), 1)
)

公式拆解 #

• TEXTBEFORE(N1:N106, " *",, TRUE)：提取 *分隔符前的内容作为零件编号；若单元格无分隔符，直接返回原内容，适配纯零件编号的录入场景。
• --(...)：将文本匹配结果（TRUE/FALSE）转换为1/0，标记属于目标零件的行。
• IFERROR(--TEXTAFTER(N1:N106, " *",, TRUE), 1)：提取分隔符后的数字并转为数值；若无分隔符则返回1，对应单个零件扫码的情况。
• SUMPRODUCT：将标记行的数量值相乘求和，得到该零件的总库存数。

旧版Excel兼容公式 #

若使用不支持TEXTBEFORE/TEXTAFTER的旧版Excel，可改用以下公式：

=SUMPRODUCT(
  --(LEFT(N1:N106, IFERROR(FIND(" *", N1:N106)-1, LEN(N1:N106)))=A2),
  IFERROR(--MID(N1:N106, FIND(" *", N1:N106)+2, LEN(N1:N106)), 1)
)

公式拆解 #

• LEFT(..., IFERROR(FIND(" *", ...)-1, LEN(...)))：通过查找 *的位置截取零件编号；无分隔符时取整个单元格内容。
• MID(..., FIND(" *", ...)+2, ...)：从分隔符后第2位开始截取数字并转成数值；无分隔符时返回1。

使用步骤 #

1. 将所有需要统计的零件编号依次填入某列（如A列），每行对应一个零件。
2. 在对应行的统计单元格（如B列）输入上述公式，下拉即可批量完成所有零件的库存统计。
3. 无论操作员录入纯零件编号（计1个）还是零件编号 *N（计N个），公式均可自动识别计算。

内容的提问来源于stack exchange，提问作者user24942277