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

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



"robotman" wrote:

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

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

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

--
Regards,
Tom Ogilvy

"robotman" wrote in message
oups.com...
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

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