如何H、I列条件组合满足D列汇总数据至E列？

lenno

如何H、I列条件组合满足D列汇总数据至E列？

part98

=SUMPRODUCT(($H$13:$H$20&$I$13:$I$20=D13)*$J$13:$J$20)

mykitami

=SUM(($H$13:$H$20&$I$13:$I$20=D13)*$J$13:$J$20)

智凡

=LOOKUP(1,0/($H$13:$H$20&$I$13:$I$20=D13),$J$13:$J$20)

用这个公式就可以实现

bsgn2017

1. =SUM(TOCOL(FILTER(H$13:J$20,H$13:H$20&I$13:I$20=D13)*1,3))

复制代码

xf0731

是这个意思不：

1. =SUMIFS($J$13:$J$20,$H$13:$H$20,LEFT(D13,2),$I$13:$I$20,RIGHT(D13,2))

复制代码

lenno

确实不错，感谢

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [h13].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
   s = arr(i, 1) & arr(i, 2)
   d(s) = d(s) + arr(i, 3)
Next
brr = [d13].CurrentRegion
For i = 2 To UBound(brr) - 1
   If d.exists(brr(i, 1)) Then
      brr(i, 2) = d(brr(i, 1))
   Else
      brr(i, 2) = 0
   End If
Next
[d13].CurrentRegion = brr
End Sub

你是那块小饼干

=SUM(FILTER($J$13:$J$20,D13=$H$13:$H$20&$I$13:$I$20,""))