# 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.

Please Help :wink:

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### 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

#### Aladin Akyurek

##### 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
9
Views
2K
Replies
8
Views
609
Replies
20
Views
307
Replies
14
Views
798
Replies
3
Views
538

Threads
1,140,938
Messages
5,703,271
Members
421,289
Latest member
fbohlandt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back