jcchiang

¦^´_ 1# b9208


C7=COUNTIFS(¸ê®Æ!$B:$B,¤u§@ªí2!$B$7,¸ê®Æ!$H:$H,¤u§@ªí2!C6)¥k©Ô
C8=COUNTIFS(¸ê®Æ!$B:$B,¤u§@ªí2!$B$8,¸ê®Æ!$H:$H,¤u§@ªí2!C6)¥k©Ô

jcchiang

¦^´_ 3# b9208

¸Õ¸Õ¬Ý
    Sub ex()
Dim arr
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("¸ê®Æ").[B5].CurrentRegion
For i = 2 To UBound(arr)
   If Not d.Exists(arr(i, 2) & arr(i, 8)) Then
      d.Add (arr(i, 2) & arr(i, 8)), 1
   Else
      d(arr(i, 2) & arr(i, 8)) = d(arr(i, 2) & arr(i, 8)) + 1
   End If
Next
With Sheets(1)
arr = .[B6].CurrentRegion
For i = 2 To UBound(arr, 2) - 1
   For j = 2 To UBound(arr)
      If d(arr(j, 1) & arr(1, i)) = "" Then
         arr(j, i) = 0
      Else
         arr(j, i) = d(arr(j, 1) & arr(1, i))
      End If
   Next
Next
.[B6].CurrentRegion = arr
.[L7].Resize(UBound(arr) - 1) = "=sum(c7:K7)"
.[L:L] = .[L:L].Value
End With
Set d = Nothing
End Sub