Using the countif functin with a wild card with text values

[biccy g]

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:

 

[Richard Schollar]

Something like this should work on column A:

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

But how are you incorporating the column B criteria?

Richard

 

[biccy g]

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]

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:

=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]

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]

Thanks Guys...... !!!