SUMPRODUCT, COUNTIF and wildcard

  • Thread starter Thread starter Epinn
  • Start date Start date
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
 
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.
Click to expand...

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,

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~*~*
Click to expand...

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.
Click to expand...

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
 
So, other than COUNTIF, SEARCH can take wildcard.
Any other functions, off hand?
Click to expand...

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~*~*
Click to expand...

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.
Click to expand...

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
 
Back
Top