# Using the countif functin with a wild card with text values

#### biccy g

##### New Member
Hi All,

Right first things first I am using Excel 200 on windows xp.

Can anybody tell me if you can do a countif function using a wildcard as I cannot seem to get this to work?

I can use the countif function over two columns fine with the exact values but cannot work out how to use this with text values and a wild card.

Column A = Description Column B = Status

I want to know how many cells equal a description begginging with 'TVI - ?????????' and equal to a particular status (column B), so column A is where I am trying to use the wildcard with text values.

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Richard Schollar

##### MrExcel MVP
Something like this should work on column A:

=COUNTIF(A1:A100,"TVI - *")

But how are you incorporating the column B criteria?

Richard

#### biccy g

##### New Member
Well I have tried a number of different ways:

=COUNT(IF(\$B\$2:\$B\$92="TVI -*",IF(\$C\$1:\$C\$92="In Review",1,0),0))

=COUNT(IF(\$B\$2:\$B\$92="TVI -*",IF(\$C\$1:\$C\$92="In Review",1,0)))

The condtinal sum wizard creates the below formula but does not give the correct value:

=SUM(IF(\$B\$2:\$B\$71="TVI -*",IF(\$C\$2:\$C\$71="In Review",1,0),0))

I am pushing my limits here so don't be afraid to tell me I am doing it completely wrong and thats why it does not work !!

#### Richard Schollar

##### MrExcel MVP
The conditional sum is giving you an array formula which must be confirmed with Ctrl+Shift+Enter (if you've entered it correctly, Excel will surround the entire formulas with curly braces):

{=SUM(IF(\$B\$2:\$B\$71="TVI -*",IF(\$C\$2:\$C\$71="In Review",1,0),0))}

However, you should be able to do this with a simple Sumproduct:

Code:
``=SUMPRODUCT((ISNUMBER(SEARCH("TVI -",\$B\$2:\$B\$71)))+0,(\$C\$2:\$C\$71="In Review")+0)``

Give it a try and shout if you get stuck.

Richard

##### MrExcel MVP
Well I have tried a number of different ways:

=COUNT(IF(\$B\$2:\$B\$92="TVI -*",IF(\$C\$1:\$C\$92="In Review",1,0),0))

=COUNT(IF(\$B\$2:\$B\$92="TVI -*",IF(\$C\$1:\$C\$92="In Review",1,0)))

The condtinal sum wizard creates the below formula but does not give the correct value:

=SUM(IF(\$B\$2:\$B\$71="TVI -*",IF(\$C\$2:\$C\$71="In Review",1,0),0))

I am pushing my limits here so don't be afraid to tell me I am doing it completely wrong and thats why it does not work !!

Wildcards cannot be used with IF...

=SUMPRODUCT(--ISNUMBER(SEARCH("TVI",\$B\$2:\$B\$71)),--(\$C\$2:\$C\$71="In Review"))

which needs just enter.

#### biccy g

##### New Member
Thanks Guys...... !!! Replies
2
Views
145
Replies
3
Views
177
Replies
5
Views
411
Replies
13
Views
91
Replies
3
Views
176