提取名字，再按名字汇总金额

冬不暖

H3站桩

1. =IFERROR(SUBSTITUTE(INDEX(B:B,SMALL(IF(ISNUMBER(FIND("联系人",$B$1:$B$20)),ROW($1:$20)),ROW(A1))),"联系人:",),"")

复制代码

I3站桩

1. =SUMPRODUCT(N(LOOKUP(ROW($2:$20),IF(ISNUMBER(FIND("联系人",$B$2:$B$20)),ROW($2:$20)))=MATCH("*"&H3,B:B,)),$F$2:$F$20)

复制代码

都需三键

一杯绿茶来了

=MID(INDEX(B:B,SMALL(IF(ISNUMBER(FIND("联系人",$B$1:$B$20)),ROW($1:$20)),ROW(A1))),5,3)
=SUM(OFFSET($F$1,MATCH("*"&H3,B:B,)-1,,INDEX(FREQUENCY(IF($B$1:$B$20<>"账单",ROW($1:$20)),IF($B$2:$B$21="账单",ROW($2:$21))),ROW(A1))))
数组三键

橒♂蝣

=SUM(OFFSET($F$2,XMATCH("*"&H9,$B$2:$B$20,2),,IF(XMATCH("*"&H10,$B$2:$B$20,2)-XMATCH("*"&H9,$B$2:$B$20,2)<0,XMATCH("*"&H9,$B$2:$B$20,2),XMATCH("*"&H10,$B$2:$B$20,2)-XMATCH("*"&H9,$B$2:$B$20,2))))

橒♂蝣

=SUBSTITUTE(FILTER(B:B,ISNUMBER(FIND("联系人",B:B))),"联系人:","")

咔咔乱坠

365函数，供参考

1. =IF({1,0},FILTER(B2:B19,"金额"=F3:F20),DROP(MMULT(IFERROR(--TEXTSPLIT(TEXTJOIN(";",,IF("金额"=F3:F20,B2:B19,F3:F20)),";","联系人:"),),ROW(1:9)^0),1))

复制代码

hcm19522

I2 下拉完成 方正確  姓名需排序=SUM(OFFSET(F$1,MATCH("*"&H3,B:B,),,99))-SUM(I4:I5)


姓名不需排序=SUMPRODUCT(ISNUMBER(FIND(K3,LOOKUP(ROW($2:$20),ROW($2:$20)/ISNUMBER(FIND("联系人",B$2:B$20)),B$2:B$20)))*N(+F$2:F$20))

陈永生

使用的是vba

1. Sub test()
   
2.     Call 填充G列
   
3.     Call 字典求和
   
4.     Call 清空G列
   
5. End Sub
   
6. 
   
7. Private Sub 填充G列()
   
8.     For i = 2 To 20
   
9.         If Left(Cells(i, 2), 3) = "联系人" Then
   
10.             Cells(i, 7) = Right(Cells(i, 2), Len(Cells(i, 2)) - 4)
    
11.         ElseIf Left(Cells(i, 2), 3) <> "联系人" And Len(Cells(i, 2)) <> 0 Then
    
12.             Cells(i, 7) = Cells(i - 1, 7)
    
13.         End If
    
14.     Next
    
15. End Sub
    
16. 
    
17. Private Sub 清空G列()
    
18.     Columns("G:G").Clear
    
19. End Sub
    
20. 
    
21. Private Sub 字典求和()
    
22.     Dim dic As New Dictionary
    
23.     For Each Rng In Range("G2:g20")
    
24.         Name = Rng.Value
    
25.          If Rng.Offset(0, -1) <> "" And IsNumeric(Rng.Offset(0, -1).Value) Then
    
26.             If dic.Exists(Name) Then
    
27.                 dic(Name) = dic(Name) + Rng.Offset(0, -1)
    
28.             Else
    
29.                 dic(Name) = Rng.Offset(0, -1)
    
30.             End If
    
31.         End If
    
32.     Next Rng
    
33.     x = dic.Keys
    
34.     y = dic.Items
    
35.    
    
36.    
    
37.     Range("h3").Resize(UBound(x) + 1) = Application.Transpose(x)
    
38.     Range("i3").Resize(UBound(x) + 1) = Application.Transpose(y)
    
39. End Sub

复制代码