# SUMIFS 与 COUNTIFS：从几万行明细里按条件和月份直接算出合计与笔数

求和区域为什么排在第一个、区域大小不一致会报什么错，以及“某个月”为什么要用两个日期条件围出来

> Excel 函数 · 表格计算 · 公式引用 · 约 7 分钟 · 10 月 28 日

## 本篇要点

1. SUMIFS 的结构是 SUMIFS(求和区域, 条件区域1, 条件1, …)，求和区域在最前，这一点和单条件的 SUMIF 正好相反。
2. 求和区域与每个条件区域的行列数必须完全一致，不一致直接报 #VALUE!；SUMIF 允许大小不同，两者不能互相套用。
3. 文字条件必须加双引号；比较符要和值拼在同一个字符串里，引用单元格时写成 ">="&单元格。
4. “某个月”用两遍同一日期列：“>=” 月初 和 "<=" EOMONTH(月初,0)，两端都包含。
5. 拖成“区域 × 月份”的表时，明细区域全部绝对引用，行标题用 $A2 锁列、月表头用 B$1 锁行。
6. SUMIFS 没匹配到时返回 0，不报错也不留空；日期或金额被存成文本会造成算不出或合计偏小，可用 ISNUMBER 检查、用分列修正。
7. COUNTIFS 是 SUMIFS 去掉求和区域，条件写法和区域约束不变；它的笔数可用来交叉验证合计是否可信。

---

这一篇要动手做的，是几乎每月都重复的事：明细表里躺着几千上万行订单，你要在上面算出一张“区域 × 月份”的汇总表——每个格子是金额合计，旁边一列是单数。做完以后，新增一个月的数据，你只需要换掉表头日期，整张表自己就变了，不用再筛选、复制、粘贴、求和。

准备两样东西。一份明细，一行一条记录，中间没有合并单元格、没有小计行（这类表为什么必须先改规范，[前面那篇](https://pangzhengboyin.com/articles/excel-tabular-data-shape-and-cleanup-98a9d6a5)讲过），日期列必须是真正的日期，不是“2024年3月”这样的文字。再加一张空表放汇总结果。

本文用一份 8 行的明细来核对结果，放在工作表 `明细` 的 A1:E9：A 列订单号、B 列日期、C 列区域、D 列销售员、E 列金额。金额依次 1200、800、1500、900、600、700、1100、400；日期依次 3/2、3/15、3/20、4/1、4/8、3/28、4/15、3/30；区域依次华东、华北、华东、华东、华南、华东、华北、华南。

## 求和区域排在第一个，后面才是成对的条件

多数人的直觉是先说条件、再说加哪一列。SUMIFS 恰好反过来：

```
=SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2, ...)
```

第一个位置放**要加总的那些数**，然后成对地写“去哪一列看、看到什么才算数”。算华东区 3 月的合计：

```
=SUMIFS(明细!$E$2:$E$9, 明细!$C$2:$C$9, "华东", 明细!$B$2:$B$9, ">="&DATE(2024,3,1), 明细!$B$2:$B$9, "<="&DATE(2024,3,31))
```

读法：把 E2:E9 的金额加起来，但只加那些“C 列是华东”**并且**“B 列不早于 2024/3/1”**并且**“B 列不晚于 2024/3/31”的行。拿这 8 行数一遍是 1200 + 1500 + 700 = 3400，能对上就说明三个条件的位置放对了。

一个对照要记住：只能写一个条件的 SUMIF 是 `SUMIF(条件区域, 条件, 求和区域)`，求和区在最后。两个函数长得像、顺序不同，从旧公式改过来时抄错位置很常见 [1]。

## 所有区域必须一样大，否则整个公式报错

上面每个区域都写成 `$2:$9`。SUMIFS 要求**求和区域和每一个条件区域的行数、列数完全一致**，不一致就直接得到 `#VALUE!`——不是算错，是整个公式不算 [2]。比如求和区域手滑写成 `$E$2:$E$8`（少一行），条件区域还是 `$2:$9`，立刻报错。这种错反而好，因为它藏不住。

难发现的是行数刚好相同、起点错开一格的情况，比如 `$B$3:$B$10` 配上 `$E$2:$E$9`：不报错，算出来却是错位的行，只能手工核对才看得出。

顺带说清一个容易混的点：**单条件的 SUMIF 不要求一样大**，文档明确说它会拿条件区域的左上角当起点、自己扩展成和求和区域一样大 [2]。所以“SUMIF 能跑、换成 SUMIFS 就 `#VALUE!`”是正常现象，不是 Excel 坏了。

## 条件怎么写

条件不是单纯的值，而是一段会被 Excel 解释的比对文本，写法决定怎么比。

**文字要带引号。** `"华东"` 是条件，`华东` 不是。少了引号，Excel 会当它是一个定义好的名称去找，找不到就显示 `#NAME?`。

**比较符要跟值拼在同一个字符串里。** `">=1000"` 可以；要引用单元格时用 `&` 拼，写成 `">="&$G$1`。

**日期用 DATE 拼，别手写日期串。** `">=2024/3/1"` 要靠本机日期格式去猜，换台电脑结果可能不同；`">="&DATE(2024,3,1)` 把日期换成 Excel 内部的日期序号再拼进去，跟设置无关。日期边界放在单元格里就直接 `">="&$B$1`，前提是那一格真的是日期。

**“某个月”是两遍同一个区域。** 它不是一次比较，而是“不早于月初”且“不晚于月末”，所以日期列出现两次，一次配 `>=`、一次配 `<=`。两边都带等号，说明边界包含在内：3 月 1 日和 3 月 31 日当天的单都算进 3 月。月末用 `EOMONTH($B$1,0)` 求：给一个日期和偏移 0，它返回那个日期所在月的最后一天，2 月和闰年都不用你记天数。

## 拖成一张表：哪一半要锁

汇总表 A2:A4 写华东、华北、华南，B1 和 C1 分别放 `2024/3/1` 和 `2024/4/1` 两个真日期，单元格格式设成 `m"月"`，屏幕显示“3月”“4月”，格子里还是日期。在 B2 写：

```
=SUMIFS(明细!$E$2:$E$9, 明细!$C$2:$C$9, $A2, 明细!$B$2:$B$9, ">="&B$1, 明细!$B$2:$B$9, "<="&EOMONTH(B$1,0))
```

三个区域用 `$` 钉死，它们永远不变；`$A2` 锁列、放开行，因为它要跟着往下走（华东→华北），不能跟着往右走；`B$1` 锁行、放开列，因为它要跟着往右走（3月→4月），但永远在第 1 行。这就是上一篇[相对、绝对、混合引用](https://pangzhengboyin.com/articles/excel-relative-absolute-mixed-references-73404c11)里说的“锁住哪一半”，只是这里两个方向各出现一次、锁法相反。

双击右下角填满 B2:C4，应该得到 3400/900、800/1100、400/600。三行两列都能拿明细对上，这张表就能长期用了。加 5 月只需把 C 列整列复制到 D 列、改 D1 的日期，公式自动变成 `>="&D$1`，其它一个字都不用动。唯一要留意的是 `$2:$9` 写死了行数，明细新增行会漏算；改成整列引用（`明细!$E:$E`）或把明细转成表格、用列名引用，范围就会自己长大。

## 出错时你会看到什么

**`#VALUE!`** → 求和区域和某个条件区域行列数不一致。逐个参数框一下，看是不是都从同一行到同一行 [2]。

**`#NAME?`** → 文字条件漏了引号，或工作表名写错、没加感叹号。

**结果是 0** → 最危险的一种。SUMIFS 一条都没匹配上时返回 `0`，不报错也不留空，和“这个区域这个月确实卖了 0 元”长得一样 [1]。看到 0 先别信：手动筛一下这个区域这个月到底有没有单；检查日期条件有没有写反（`>=` 配成 `<=`，区间就空了）；检查日期列是不是被存成了文本——文本“2024-03-05”在比较大小时不被当作日期，结果常常是算不出来或报 `#VALUE!`。用 `=ISNUMBER(明细!B2)` 查一格，返回 FALSE 就是文本，用“数据 → 分列”转成真日期。

**合计明显偏小** → 金额列里混着“看着是数字”的文本，SUMIFS 不会把它们加进去，直接跳过。同样用 `ISNUMBER` 查，同样用分列转。

**条件格是空的** → COUNTIFS、SUMIFS 会把空单元格当 0 来比 [3]，于是变成“金额 >= 0”这类宽得离谱的条件，把不该算的都算进来。所以表头和条件格先填好再拖。

## 笔数：拿掉求和区域就是 COUNTIFS

COUNTIFS 就是 SUMIFS 去掉第一个参数：

```
=COUNTIFS(条件区域1, 条件1, 条件区域2, 条件2, ...)
```

在 E2 写：

```
=COUNTIFS(明细!$C$2:$C$9, $A2, 明细!$B$2:$B$9, ">="&B$1, 明细!$B$2:$B$9, "<="&EOMONTH(B$1,0))
```

填满后华东 3 月 3 单、4 月 1 单，华北和华南各月各 1 单。区域约束、条件写法都和 SUMIFS 完全一样。

笔数不只是给人看的，它顺手解决了上面那个“0 到底是什么”的问题：笔数 0、合计 0，仍然要再查明细才知道是不是真没数据；但笔数是 3、合计却是 0，那几乎一定是金额列存成了文本，而不是没有数据。

## 每月只做三件事

把新月份的明细贴进 `明细`（行数变多就改用整列或表格引用）；在汇总表新增一列，只填表头日期；把上一列整列复制过去。

核对顺序建议固定：挑一个月一个区域，用筛选手工加一遍和 SUMIFS 比，这一步能同时抓住区域写错和金额是文本；再把某一行各月加起来，和全年合计对一下；最后让笔数列合计等于明细的有效行数，不等就说明有条件写宽了，最常见的是日期边界。

<details><summary>两个自测题（先自己想，再展开）</summary>

1. 把 3 月那一列复制成 5 月，除了改表头日期，公式里还要改什么？
2. 某个格子显示 0，怎么判断是“这个月没卖”还是“公式有问题”？

参考答案：

1. 什么都不用改。区域都锁死了，月份通过 `B$1` 跟着列走，`EOMONTH` 会自动算出 5 月的最后一天。
2. 先看旁边的 COUNTIFS 笔数，再用筛选确认明细里有没有这个区域、这个月的记录；笔数不为 0 而合计为 0，就查金额列是不是文本。
</details>

一句话收束：SUMIFS 第一个位置是“要加的数列”，后面成对写“看哪列、认什么”；所有区域必须一样大；月份是靠两个日期条件围出来的区间；返回 0 从不能证明“真的没有”，要靠笔数和抽查去确认。

## 思维导图

```mermaid
flowchart LR
  A["明细表<br>日期 区域 销售员 金额“] --> B[”SUMIFS<br>求和区域在最前“]
  A --> C[”COUNTIFS<br>只数不加“]
  B --> D[”条件成对写<br>区域 + 条件“]
  C --> D
  D --> E[”文字带引号“]
  D --> F[”比较符拼值<br>>=&单元格“]
  D --> G[”区间写两遍<br>>=月初 且 <=EOMONTH“]
  B --> H[”区域必须一样大<br>否则 #VALUE!“]
  B --> I[”无匹配返回 0<br>与真 0 无法区分“]
  I --> J[”用 COUNTIFS 笔数交叉验证“]
  B --> K[”拖表: 区域全锁<br>$A2 锁列 / B$1 锁行"]
```

## 术语表

- SUMIFS：按多个条件同时成立来加总的函数，先写要加的数列，再成对写“去哪一列看、看到什么才算”。
- COUNTIFS：只数行数不加总的版本，去掉求和区域，其余参数和 SUMIFS 一样。
- 条件（criteria）：一段被 Excel 解释的比对规则，不是单纯的值；文字要带引号，比较符写在里面。
- EOMONTH：给一个日期和月份偏移量，返回那个月的最后一天，用来把“某个月”变成一个日期区间的右端。

## 来源

1. [SUMIFS function — Microsoft Support：参数顺序、条件写法、通配符，以及与 SUMIF 顺序相反](https://support.microsoft.com/en-us/excel/functions/sumifs-function)
2. [How to correct a #VALUE! error in the SUMIF/SUMIFS function — 求和区域与条件区域行列数必须一致，SUMIF 允许不同大小](https://support.microsoft.com/en-us/excel/how-to-correct-a-value-error-in-the-sumif-sumifs-function)
3. [COUNTIFS function — Microsoft Support：参数配对、区域大小要求、空单元格条件按 0 处理](https://support.microsoft.com/en-us/excel/functions/countifs-function)

---

原文：https://pangzhengboyin.com/articles/sumifs-countifs-conditional-totals-by-month-9d1c7b29

> **庞征博引** · 想学的，慢慢都会
>
> 庞征博引是把想学的东西写成连载的 AI 学习工具：每篇 5–10 分钟，边读边问，接下来学什么跟着你走。这篇就是这样写出来的。
>
> 开始你自己的连载 → https://pangzhengboyin.com