Biff, I think I should start my own thread ......

It is interesting that we can use wildcard with COUNTIF but not with SUMPRODUCT.

=COUNTIF(C:C,"A*")
or
=COUNTIF(C1:C1000,"A*")

We can't use column nor wildcard with SUMPRODUCT for version 2003. (As per Biff, okay to use column for 2007.)

So, we have to use LEFT( ).

=SUMPRODUCT(--(LEFT(C1:C1000)="A"))

Now, if I want to use COUNTIF and my criterion is "A*" when "*" is not wildcard this time, how do I tell Excel? The tilde sign "~" seems to work. Can someone confirm, please?

=COUNTIF(C:C,"A~*")
or
=COUNTIF(C1:C1000,"A~*")
or
=SUMPRODUCT(--(LEFT(C1:C1000,2)="A*"))

If I want "A**" is there an easier way than A~*~*

Thanks!

Epinn