SumProduct - Multiple Criteria - VBA

[IanSavage]

I am trying to work on a set of data, where I need to count how many times certain combinations of codes are used. Originally I had just one set of codes to count, but now I have multiple code combinations to count.

Is it possible to combine the two lines of code as below, to work together in VBA? I know they work individually, but now I need something to work with the multiple criteria, plus place the relevant answers in different cells. In total, I have something in the region of 15 'AB' codes and 20'RC' codes.

Worksheets("Count Codes").Range("B2").Value = Worksheets("RC Code Data").Evaluate("SumProduct(--(Left(B4:B1000, 4) = ""AB10""))")
Worksheets("Count Codes").Range("C2").Value = Worksheets("RC Code Data").Evaluate("SumProduct(--(Left(F4:F1000, 4) = ""RC10""))")

I've been scratching my head, and its beating me so far.

Any help will be gratefully appreciated!!

Ian

 

[Fluff]

How about

Sub IanSavage()
   Dim Ary As Variant, x As Variant
   Dim i As Long
   
   Ary = Array("AB10", "AB11", "AB09")
   For i = 0 To UBound(Ary)
   Worksheets("Count Codes").Range("B2").Offset(i).Value = Worksheets("RC Code Data").Evaluate("SumProduct(--(Left($B$4:$B$1000, 4) =" & Chr(34) & Ary(i) & Chr(34) & "))")
   Next
End Sub