如何让SUMPRODUCT公式识别动态生成的单元格范围以统计关联唯一值

兄弟，我完全懂你的困扰——你已经能精准算出对应姓名在C列的起止行，把它们拼成类似C7:C12的文本字符串，但SUMPRODUCT根本不买账，因为它需要的是真实的单元格区域引用，不是一串文本格式的地址。别慌，我给你几个实用的解决思路：

方法1：用INDIRECT把文本地址转成真实引用 #

既然你已经能生成正确的范围文本，只要用INDIRECT()函数把它转换成SUMPRODUCT能识别的单元格引用就行。把你之前的公式修改成这样：

=SUMPRODUCT(1/COUNTIF(INDIRECT("C"&MATCH(D5,$A$1:$A$3879,0)&":"&"C"&MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , ))), INDIRECT("C"&MATCH(D5,$A$1:$A$3879,0)&":"&"C"&MAX(INDEX(ROW($1:$3879)*($A$1:$A$3879=D5), , )))))

INDIRECT的作用就是解析文本格式的单元格地址，把它变成实实在在的单元格区域，这样SUMPRODUCT就能正常执行唯一值统计了。不过要注意：INDIRECT是易失性函数，每次工作表有变动都会重新计算，如果你的数据量很大（比如几千行），可能会拖慢表格速度。

方法2：直接在SUMPRODUCT里加条件判断（推荐） #

其实不用费劲找起止行，我们可以直接在SUMPRODUCT里加入姓名匹配的条件，一步到位统计唯一值，还能避免易失性函数的问题。公式如下：

=SUMPRODUCT(($A$1:$A$3879=D5)/COUNTIFS($A$1:$A$3879,$A$1:$A$3879,$C$1:$C$3879,$C$1:$C$3879))

逻辑拆解： #

1. $A$1:$A$3879=D5：生成一个由TRUE/FALSE组成的数组，标记出所有和D5姓名匹配的行；
2. COUNTIFS($A$1:$A$3879,$A$1:$A$3879,$C$1:$C$3879,$C$1:$C$3879)：同时匹配姓名和C列值，每个唯一的「姓名+C值」组合只会被计数1次；
3. 用条件数组除以计数数组，最后SUMPRODUCT求和，就得到了该姓名对应的C列唯一值数量。

这个方法不用找范围，直接全列判断，计算效率更高，数据量大的时候更稳定。

方法3：Excel 365/2021专属简洁写法 #

如果你用的是Excel 365或2021版本，直接用FILTER+UNIQUE+COUNTA组合，逻辑更直观：

=COUNTA(UNIQUE(FILTER($C$1:$C$3879,$A$1:$A$3879=D5)))

逻辑拆解： #

1. FILTER($C$1:$C$3879,$A$1:$A$3879=D5)：筛选出所有和D5姓名匹配的C列值；
2. UNIQUE()：对筛选结果去重；
3. COUNTA()：统计去重后的非空值数量，就是你要的唯一值个数。

这个写法最容易理解，代码也最短，适合新版本Excel用户。

备注：内容来源于stack exchange，提问作者user1814018