COUNTIF and wildcards

[mpenkala]

Hi again,

I have a stats sheet that currently looks at another worksheet and uses
COUNTIF functions to total up certain categories. I've run into a problem
with the following:

Under "Field Goals Made" I was hoping to use the following:

=COUNTIF(PbyPQ1!$A$3:$A$150,"Matt Penkala - Jump Shot:Made")

Problem is, the sheet that this formula gets the data from (PbyPQ1) varies
in the wording. If that is the first basket Matt Penkala made then it show's
like this:

Matt Penkala - Jump Shot:Made [2 PTS]

Next would be:

Matt Penkala - Jump Shot:Made [4 PTS]

So I only want to search the first part (Matt Penkala - Jump Shot:Made). I
keep track of the points in another section so I don't need it here. I've
read through some old posts and it seems like I need to use the LEFT
function, just not sure how.

Thanks!
Matt

 

[Pete_UK]

You can use wildcard characters in COUNTIF, so try it like this:

=COUNTIF(PbyPQ1!$A$3:$A$150,"Matt Penkala - Jump Shot:Made*")

No need for LEFT.

Hope this helps.

Pete

 

[mpenkala]

Hey Pete,

thanks a bunch, this works perfect.

Cheers,
Matt

You can use wildcard characters in COUNTIF, so try it like this:

=COUNTIF(PbyPQ1!$A$3:$A$150,"Matt Penkala - Jump Shot:Made*")

No need for LEFT.

Hope this helps.

Pete

Hi again,

I have a stats sheet that currently looks at another worksheet and uses
COUNTIF functions to total up certain categories. I've run into a problem
with the following:

Under "Field Goals Made" I was hoping to use the following:

=COUNTIF(PbyPQ1!$A$3:$A$150,"Matt Penkala - Jump Shot:Made")

Problem is, the sheet that this formula gets the data from (PbyPQ1) varies
in the wording. If that is the first basket Matt Penkala made then it show's
like this:

Matt Penkala - Jump Shot:Made [2 PTS]

Next would be:

Matt Penkala - Jump Shot:Made [4 PTS]

So I only want to search the first part (Matt Penkala - Jump Shot:Made). I
keep track of the points in another section so I don't need it here. I've
read through some old posts and it seems like I need to use the LEFT
function, just not sure how.

Thanks!
Matt

 

[Pete_UK]

Glad to hear that, Matt - thanks for feeding back.

Pete