求助！！根据姓名汇总求和，谢谢

fyq80088

求助！！根据姓名汇总求和，谢谢

france723

如果单元二的姓名也是自动生成, VBA 代码如下 (无需手打单元二的姓名, 自动生成所有姓名)

1. Sub aaa()
   
2. Dim d, y, ar, i, c, n, s, m, j
   
3. y = Range("a65536").End(3).Row
   
4. ar = Range("a3:b" & y)
   
5. Set d = CreateObject("scripting.dictionary")
   
6. For i = 1 To UBound(ar)
   
7.     d(ar(i, 2)) = d(ar(i, 2)) & "," & ar(i, 1)
   
8. Next i
   
9. n = 3
   
10. For Each c In d.keys
    
11.     m = 0
    
12.     Cells(n, 4) = c
    
13.     s = Split(Mid(d(c), 2), ",")
    
14.         For j = 0 To UBound(s)
    
15.             m = m + s(j)
    
16.         Next j
    
17.     Cells(n, 5) = m
    
18.     n = n + 1
    
19. Next c
    
20. End Sub

复制代码

qqyyh

=SUMIF($B$3:$B$19,D3,$A$3:$A$19)
=SUMPRODUCT(($B$3:$B$19=D3)*$A$3:$A$19)

france723

如果单元二的姓名你也是手打, VBA 代码如下

1. Sub bbb()
   
2. Dim y1, y2, ar, br, s, i, j
   
3. y1 = Range("a65536").End(3).Row
   
4. ar = Range("a3:b" & y1)
   
5. y2 = Range("d65536").End(3).Row
   
6. br = Range("d3:e" & y2)
   
7. For i = 1 To UBound(br)
   
8.     s = 0
   
9.     For j = 1 To UBound(ar)
   
10.         If br(i, 1) = ar(j, 2) Then
    
11.             s = s + ar(j, 1)
    
12.         End If
    
13.     Next j
    
14.     br(i, 2) = s
    
15. Next i
    
16. Range("d3").Resize(UBound(br), 2) = br
    
17. End Sub

复制代码

grf1973

两种方法。

fyq80088

谢谢，各位

zhangmengyuan66

=SUMIF($B$3:$B$19,D3,$A$3:$A$19)
=SUMPRODUCT(($B$3:$B$19=D3)*$A$3:$A$19)