SUMPRODUCT, COUNTIF and wildcard

E

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
 
B

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
 
E

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

Biff said:
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
 
B

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

Biff said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top