双条件去重计数

zhangcheng6688

学习一下字典
Sub test()
arr = [b1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   If Not d.exists(arr(i, 1)) Then
      Set d(arr(i, 1)) = CreateObject("scripting.dictionary")
   End If
   d(arr(i, 1))(arr(i, 3)) = ""
Next
ReDim brr(1 To d.Count, 1 To 2)
For Each kk In d.keys
   m = m + 1
   brr(m, 1) = kk
   brr(m, 2) = d(kk).Count
Next
[n4].Resize(m, 2) = brr
End Sub

你是那块小饼干

1. =LET(T,TEXTSPLIT(UNIQUE(B2:B19&","&D2:D19),",",,TRUE),GROUPBY(T,T,COUNTA,,0))

复制代码

waternew

=INDEX(UNIQUE(TEXTSPLIT(UNIQUE($B$2:$B$19&$D$2:$D$19),"省",)&"省"),ROW(A1),1)

=COUNTA(UNIQUE(INDEX(FILTER($B$2:$D$19,$B$2:$B$19=N4),,3)))

mjm04

一步到位的

=LET(_a,UNIQUE(HSTACK(B2:B19,D2:D19)),_b,CHOOSECOLS(_a,1),_c,{"省份","计数"},VSTACK(_c,GROUPBY(_b,_b,COUNTA)))