如何在Excel表格中跨多行按角色仅统计一次约定工时总和

你现有的员工年假申请表格结构如下：

你当前使用的公式=SUMIFS([Contract hours],[Full Name],[@[Full Name]],[Role],[@Role])会累加指定员工指定角色下所有行的工时数据，同角色多条申请记录会导致工时重复统计。

要实现「同员工同角色仅统计1次工时，再汇总所有角色工时」的需求，可按Excel版本选择对应公式：

方案1：Excel 365/2021及以上版本（极简写法） #

=SUM(UNIQUE(FILTER([Contract hours],[Full Name]=[@[Full Name]])))

公式逻辑：先筛选出当前员工的所有合同工时数据，再对工时值去重后求和。如果同一员工同一角色对应工时值唯一，该公式可直接输出正确结果。

如果存在不同角色工时值相同的场景，可使用更严谨的写法：

=SUM(UNIQUE(FILTER([Contract hours],([Full Name]=[@[Full Name]])*([Role]=[Role]))))

方案2：兼容旧版本Excel（2019及更早） #

=SUMPRODUCT(([Full Name]=[@[Full Name]])*[Contract hours]/COUNTIFS([Full Name],[Full Name],[Role],[Role],[Contract hours],[Contract hours]))

公式逻辑：对每个「姓名+角色+工时」的重复组合，按重复次数拆分单条记录的权重，求和后等价于每个角色仅统计一次工时的结果。

两种方案最终输出的Total Contracted Hrs结果均为40，符合需求。

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