wildcards in countif

I have a column (D) with text values and a term to find in column (L). If  
the term in L is exactly what is written in D then there isn't a problem  
using the following equation: @COUNTIF(D4..D285,L3)

The problem I have is when I try to use wildcards. I have tried the  
following both in the formula and in a cell for column L:

"*" L3 "*"
"*" & L3 & "*"
"*" #AND# L3 #AND# "*"

Could someone please let me know the proper form for using a variable  
criteria in the CountIf function. The help manual isn't forthcoming and I  
couldn't find anything online

Thank you,

Dale



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

Dale Shumaker wrote:
> I have a column (D) with text values and a term to find in column (L). 
> If the term in L is exactly what is written in D then there isn't a 
> problem using the following equation: @COUNTIF(D4..D285,L3)
> 
> The problem I have is when I try to use wildcards. I have tried the 
> following both in the formula and in a cell for column L:
> 
> "*" L3 "*"
> "*" & L3 & "*"
> "*" #AND# L3 #AND# "*"
> 
> Could someone please let me know the proper form for using a variable 
> criteria in the CountIf function.

Whenever you want to count cells CONTAINING a target string (rather than 
matching  it), @COUNTIF will not do.  In fact (as far as I know), in QP the task

cannot be accomplished with a single formula; you'll need a helper column, e.g.,

in E4:

   @FIND(L$3,D4,0)

or, if you're a purist ...

   @NOT(@ISERR(@FIND(L$3,D4,0)))

With one of the above formulas copied down to row 285, the result you're looking

for is given by:

   @PURECOUNT(E4..E285)


Cheers,
Uli

Thank you for the time to respond. Unfortunately this means a few extra  
columns to find all of the data. I had also tried using the notebook query  
but I keep getting an error that the ouput block doesn't have field names.

I will use the two formulas.

Dale

On Tue, 29 Jan 2008 21:07:22 -0600, Uli <nojunkmail@spamless.com> wrote:

>
> Dale Shumaker wrote:
>> I have a column (D) with text values and a term to find in column (L). 

>> If the term in L is exactly what is written in D then there isn't a  
>> problem using the following equation: @COUNTIF(D4..D285,L3)
>>  The problem I have is when I try to use wildcards. I have tried the  
>> following both in the formula and in a cell for column L:
>>  "*" L3 "*"
>> "*" & L3 & "*"
>> "*" #AND# L3 #AND# "*"
>>  Could someone please let me know the proper form for using a variable 

>> criteria in the CountIf function.
>
> Whenever you want to count cells CONTAINING a target string (rather than  
> matching  it), @COUNTIF will not do.  In fact (as far as I know), in QP  
> the task cannot be accomplished with a single formula; you'll need a  
> helper column, e.g., in E4:
>
>    @FIND(L$3,D4,0)
>
> or, if you're a purist ...
>
>    @NOT(@ISERR(@FIND(L$3,D4,0)))
>
> With one of the above formulas copied down to row 285, the result you're  
> looking for is given by:
>
>    @PURECOUNT(E4..E285)
>
>
> Cheers,
> Uli
>



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

@CountIf does not accept wildcards as Excel's =CountIf.  If it did, your 
first approach would have worked.

A two column approach might work but I am not sure what formulas I would 
use.  Not sure how @Find would work though.  It works fine for a one to 
one approach but one would need a column for each cell in column E and 
then an @sum to sum that column as Uli detailed. e.g. 
@NOT(@ISERR(@FIND($E$2,D2,0)))

@DCOUNT and Notebook Query might help but I am not sure how it would 
apply in this case.

A recalc is needed for some of these methods.

I think a macro approach might be the best route.

Yes I finally used multiple columns using the  
@NOT(@ISERR(@FIND($E$2,D2,0))) formula followed by summing the entire  
column.

The time consuming part is prior to this when I have to eliminate  
duplicates. The duplicates are based on at least four cells in each row,  
where only one of the four has to be different. Right now I sort on the  
columns of interest and set up an equation to determine if adjacent rows  
are identical. If so the formula is copied as values to another column and  
resorted. The duplicates are then deleted. Repeat until no more duplicates  
exist.

Thank you to everyone for your help.

Dale



On Wed, 30 Jan 2008 12:14:51 -0600, Kenneth Hobson <khobson@aaahawk.com> 

wrote:

>
> @CountIf does not accept wildcards as Excel's =CountIf.  If it did, your  
> first approach would have worked.
>
> A two column approach might work but I am not sure what formulas I would  
> use.  Not sure how @Find would work though.  It works fine for a one to  
> one approach but one would need a column for each cell in column E and  
> then an @sum to sum that column as Uli detailed. e.g.  
> @NOT(@ISERR(@FIND($E$2,D2,0)))
>
> @DCOUNT and Notebook Query might help but I am not sure how it would  
> apply in this case.
>
> A recalc is needed for some of these methods.
>
> I think a macro approach might be the best route.
>



-- 
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/