Sumproduct, Left and #Value!

[IanSavage]

I'm trying to count how many times specific text (the first 4 characters in a cell) appear in a column. Each cell starts with a code e.g. RC30 and then will have varying text after.

I've tried VBA code

Worksheets("Sheet1").Range("D1").Value = Worksheets("Sheet1").Application.SumProduct(--(Left("A1:A21", 4) = "RC30"))

but in cell D1 where I want the answer, I get #Value !

Now I know I'm probably going about this all wrong, VBA is not my strong point but I do find it fascinating to use when I get the coding correct.

Any help / guidance will be appreciated.

Ian

 

[Phuoc]

Try:

Worksheets("Sheet1").Range("D1").Value = Evaluate("SumProduct(--(Left(A1:A21, 4) = ""RC30""))")

 

[IanSavage]

Thank you!