SUMPRODUCT, COUNTIF and wildcard

[Epinn]

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

 

[Biff]

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_character>1"

=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

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

 

[Epinn]

Biff,

Thank you for taking the time to teach me.

So, other than COUNTIF, SEARCH can take wildcard. Any other functions, off hand?

The wildcard ? follows the general rule.

Looks like a*1 is treated as a*

Cheers,

Epinn

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_character>1"

=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

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

 

[Biff]

So, other than COUNTIF, SEARCH can take wildcard.

Any other functions, off hand?

Match, V/Hlookup, Find, Sumif

There may be one or two others that I'm forgetting.

Biff

Biff,

Thank you for taking the time to teach me.

So, other than COUNTIF, SEARCH can take wildcard. Any other functions, off
hand?

The wildcard ? follows the general rule.

Looks like a*1 is treated as a*

Cheers,

Epinn

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_character>1"

=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

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