Using the countif functin with a wild card with text values

biccy g

New Member
Joined
Oct 17, 2006
Messages
8
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 17, 2006
Messages
8
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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Feb 14, 2002
Messages
85,209
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.
 

Forum statistics

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

Which adblocker are you using?

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
Top