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

I think that's about as easy as it can get!

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

You can use wildcards in other functions (that support wildcards) within
Sumproduct:

..................A..............B.............C
1..........A1234...........Y............10
2..........A4321...........Y............22
3..........A1123...........Y............37

Criteria: sum column C where column B = "Y" and column A =
"A<any_single_character1"

=SUMPRODUCT(--(ISNUMBER(SEARCH("a?1",A1:A3))),--(B1:B3="Y"),C1:C3)

Returns: 37

=SUMPRODUCT(--(ISNUMBER(SEARCH("a*1",A1:A3))),--(B1:B3="Y"),C1:C3)

Would return the incorrect result. It would include EVERY entry from column
A as meeting the search criteria.

Biff

"Epinn" wrote in message
...
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