基于参考表对逗号分隔值求和的Excel公式求助

针对你遇到的多选下拉单元格（逗号分隔值）匹配Sheet2分数求和的需求，直接用拆分+匹配+求和的组合公式就能解决，而且支持新增分隔值自动计算：

=SUM(IFNA(XLOOKUP(TRIM(TEXTSPLIT(C2, ",")), Sheet2!$B$2:$B$35, Sheet2!$A$2:$A$35, 0), 0))

公式拆解说明： #

• TEXTSPLIT(C2, ",")：将目标单元格的逗号分隔值拆分为独立数组（比如把"Leadership, Activity on Campus"拆成{"Leadership", " Activity on Campus"}）
• TRIM(...)：清除拆分后每个值的前后空格，避免多选下拉自动添加的空格导致匹配失败
• XLOOKUP(...)：用拆分后的每个值匹配Sheet2的B列（项目名称），返回对应A列的分数，匹配不到时返回0
• IFNA(..., 0)：处理匹配错误，确保无对应项时不产生#N/A错误，而是计入0分
• SUM(...)：将所有匹配到的分数相加，得到最终总分

适配你的示例场景： #

• John Smith的目标单元格若为"Varsity Sport, Leadership"，公式会拆分后分别匹配9分和9分，求和得18分
• Jane Doe的目标单元格若为"Leadership Level 1, Leadership"，会匹配5分和9分，求和得14分

注意事项： #

• 确保Sheet2的B列（项目名称）无重复值，否则XLOOKUP仅返回第一个匹配项的分数
• 新增项目到Sheet2的B2:B35范围后，公式会自动识别，无需修改公式范围；若要更灵活，可将范围改为Sheet2!$B:$B，但限定有效范围能提升计算效率
• 若目标单元格为空，公式返回0，符合你对空值的处理需求

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