如何用更简洁的Excel SUMIFS公式实现首字符为字母的求和？

针对你需要的首字符为任意字母且Sheet2!C:C等于A2时求和Sheet2!B:B的需求，以下是几种比暴力累加更简洁的实现方式：

方案1：兼容性最强（支持所有Excel版本）—— SUMPRODUCT函数 #

=SUMPRODUCT(Sheet2!B:B, --(Sheet2!C:C=A2), --(ISNUMBER(SEARCH(LEFT(Sheet2!J:J,1),"abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

• 逻辑说明：
  • --(Sheet2!C:C=A2)：将"Sheet2!C列等于A2"的布尔判断转为1/0数值
  • ISNUMBER(SEARCH(...))：判断Sheet2!J列单元格首字符是否在大小写字母范围内（SEARCH不区分大小写，若需严格区分可替换为FIND）
  • 三个数组相乘后求和，等价于满足双条件的B列值累加

方案2：Excel 365/2021专属（最简洁）—— SUM+FILTER+REGEXMATCH #

利用Excel 365的动态数组和正则匹配能力，代码更直观：

=SUM(FILTER(Sheet2!B:B, (Sheet2!C:C=A2)*(REGEXMATCH(Sheet2!J:J, "^[a-zA-Z]")), 0))

• 逻辑说明：
  • REGEXMATCH(Sheet2!J:J, "^[a-zA-Z]")：直接匹配首字符为大小写字母的单元格
  • FILTER筛选出同时满足"Sheet2!C:C=A2"和首字符为字母的B列数据
  • 最后用SUM对筛选结果求和

方案3：数组式SUMIFS（简化暴力写法） #

如果不想用SUMPRODUCT或正则，也可以用数组形式一次性传入所有字母通配符：

=SUM(SUMIFS(Sheet2!B:B, Sheet2!C:C, A2, Sheet2!J:J, {"A*","B*","C*","D*","E*","F*","G*","H*","I*","J*","K*","L*","M*","N*","O*","P*","Q*","R*","S*","T*","U*","V*","W*","X*","Y*","Z*","a*","b*","c*","d*","e*","f*","g*","h*","i*","j*","k*","l*","m*","n*","o*","p*","q*","r*","s*","t*","u*","v*","w*","x*","y*","z*"}))

（注：旧版Excel需按Ctrl+Shift+Enter触发数组计算，新版Excel自动支持数组运算）

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