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:
 

Some videos you may like

Excel Facts

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

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,201
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,917
Messages
5,545,024
Members
410,647
Latest member
bernardazar
Top