countif formula help

[John Caines]

Hello All.
I have a COUNTIF formula that is as follows;

=COUNTIF($H$25:$H$65536,"Europe")

Thing is,,, it finds,,, sorry counts all occurrences with Europe in Column H.
What my problem is is now I have some that say EUROPE-USA,,,, But I still want to count this.

Is there any way to Change the formula so it says,, "Hey,, if Europe Is mentioned in a cell,, regardless of what is mentioned also,,, count it pleas"

Only,, I don't know how to code this

I hope the above makes sense.
If anybody can show me that would be great.
Many thanks for all your time.
Regards
John Caines

 

[MrKowz]

Try:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Europe",$H$25:$H$65536))))

 

[John Caines]

Many thanks for your reply MrKowz,,,
great stuff,,,, I shall try this now.....

As a note,,, been hunting on the forum and this seems to work also,,,

=COUNTIF($H$25:$H$65536,"Europe*")

I think so,,, I best test it a bit more,,, just adding the * sign in the formula....
I never knew about this..

Many thanks again for your reply MrKowz.

I'll try it now

Best regards
John Caines

 

[T. Valko]

Many thanks for your reply MrKowz,,,
great stuff,,,, I shall try this now.....

As a note,,, been hunting on the forum and this seems to work also,,,

=COUNTIF($H$25:$H$65536,"Europe*")

I think so,,, I best test it a bit more,,, just adding the * sign in the formula....
I never knew about this..

Many thanks again for your reply MrKowz.

I'll try it now

Best regards
John Caines

The asterisk is a wildcard and means "any character or characters or no characters at all".

So:

COUNT IF cells in the range H25:H65536 contain an entry that starts with the string "Europe" followed by anything or nothing.

The ? question mark is also a wildcard and means "any single character".

Note that wildcards only work on TEXT.

Functions that support wildcards:

SUMIF - all versions of Excel
COUNTIF - all versions of Excel
SEARCH - all versions of Excel
MATCH - all versions of Excel
HLOOKUP - all versions of Excel
VLOOKUP - all versions of Excel
SUMIFS - Excel 2007 and later
COUNTIFS - Excel 2007 and later
AVERAGEIF - Excel 2007 and later
AVERAGEIFS - Excel 2007 and later