SUMPRODUCT in VBA

[robotman]

I'm trying to count the occurance of a specified first three letters
in a list of values in a column. On the spreadsheet level, I can use:

= SUMPRODUCT(--(LEFT(TRIM(UPPER(C3:C1000)),3)="RED"))


When I try this in VBA, I get an syntax error.

Application.SUMPRODUCT(--(LEFT(TRIM(UPPER(C3:C1000)),3)="RED"))
OR
Worksheetfunction.SUMPRODUCT(--(LEFT(TRIM(UPPER(C3:C1000)),
3)="RED"))


Can someone explain what I'm doing wrong? I don't need to use
SUMPRODUCT, if something else like COUNTIF will work with the LEFT,
TRIM, and UPPER.

Thanks.

John

 

[Guest]

I believe I've seen people use
Execute(Sumproduct .. )

 

[Tom Ogilvy]

application.Countif(Range("C3:C1000"),"RED*")

this is case insensitive so both RED and red would be counted.

 

[robotman]

Thanks for the CountIf syntax. Good thing it's not case sensitive.

Since I may have leading spaces, I had to add a "*" to the condition
"*RED*" since I can't embed the TRIM inside the CountIf. But this
will work fine since "RED" doesn't appear in parts of other words in
the column.

Thanks again!

John