Excel中使用SUMIFS汇总文本型工时，如何用VALUE()作sum_range？

原公式问题点 #

• SUMIFS的语法要求是SUMIFS(求和区域, 条件区域1, 条件1)，你直接给求和区域套VALUE()，且将UNIQUE返回的部门数组作为条件，不符合函数的参数逻辑，导致报错。
• 移除VALUE()后返回0，是因为文本型数字无法被SUMIFS识别为数值参与求和，最终结果为0。

可行解决方案 #

由于工作表锁定无法修改单元格格式，需通过公式转换文本为数值并正确匹配部门求和：

方案1（适配Excel 365/2021及以上版本） #

使用BYROW+LAMBDA遍历每个部门，结合SUMIFS计算总工时，公式直接生成所有部门的结果数组：

=BYROW(UNIQUE(WeeklyPerformance[Department]),LAMBDA(dept,SUMIFS(--WeeklyPerformance[Hours Worked],WeeklyPerformance[Department],dept)))

• --WeeklyPerformance[Hours Worked]：强制将文本型数字转换为数值，效果等同于VALUE()，数组场景下更高效
• UNIQUE提取不重复部门列表，BYROW逐个传入部门给LAMBDA，计算对应部门的总工时

方案2（适配旧版Excel，无动态数组功能） #

先手动提取不重复部门到一列（比如A列），然后在相邻单元格输入公式并下拉：

=SUMPRODUCT(--(WeeklyPerformance[Department]=A2),--WeeklyPerformance[Hours Worked])

• --(WeeklyPerformance[Department]=A2)：生成匹配当前部门的布尔数组，强制转换为1/0
• 两个数组相乘后求和，实现对应部门的文本工时数值求和

若想一次性生成所有结果，需按Ctrl+Shift+Enter输入数组公式：

=SUMIFS(--WeeklyPerformance[Hours Worked],WeeklyPerformance[Department],TRANSPOSE(UNIQUE(WeeklyPerformance[Department])))

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