Is it possible to use a wild card in a =COUNT(IF equation?

[Guest]

I'm trying to use a wild card in the following equation:
=COUNT(IF((B7:B24=$B24)*(I7:I24="C*"),B7:B24))
I tested the same equation with out the wild card by replacing it with the
exact text and it worked fine. What am I doing wrong?

 

[Roger Govier]

Hi

Try the array formula
{=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B24))}
Commit using CTRL+SHIFT+ENTER, do not enter the curly braces yourself, Excel
will insert them for you.

or the non-array formula
=SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C"))

Regards

Roger Govier

 

[Guest]

That's it! Thanks...

I also discovered that it's possible to increase the text field to "Cont:"
but there also needs to be one other change: (LEFT(I7:I24,5)="Cont:"). A "5"
needs to be added after the range to designate the length of the text.

Thanks again...

 

[Roger Govier]

Hi
You're welcome.
Yes the LEFT() function is truly LEFT(A1,n) where n is the number of
characters you require. If n is omitted, it defaults to 1 which I tend to
use when looking for either the first character of a cell or RIGHT() for the
last character. (basically I'm lazy and type the minimum characters in a
formula!!)

From your posting it looked as though you only wanted text beginning with
"c". I'm glad you worked out the requirement for 5 in your revised example.

Regards

Roger Govier

 

[Guest]

Hi Roger, I have a follow on question: Is it possible to have three arguments
in this equation? One of them needs to be occurences within a date range.

Thanks again,
Jason.

 

[Roger Govier]

Hi Jason

The answer is Yes, but I think you need 4 arguments if you want a date
range, >=lowerdate, <=upperdate.

{=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C")*(yourdaterange>=DATE(2005,3,1)*(yourdaterange<=DATE(2005,6,30),B7:B24))}
would give values for dates between 1st March and 30th June 2005.

Personally I much prefer the non-array entered SUMPRODUCT solution
=SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C"),--(yourdaterange>=DATE(2005,3,1),--(yourdaterange<=DATE(2005,6,30))

Regards

Roger Govier

 

[Guest]

Thanks Roger!

Hi Jason

The answer is Yes, but I think you need 4 arguments if you want a date
range, >=lowerdate, <=upperdate.

{=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C")*(yourdaterange>=DATE(2005,3,1)*(yourdaterange<=DATE(2005,6,30),B7:B24))}
would give values for dates between 1st March and 30th June 2005.

Personally I much prefer the non-array entered SUMPRODUCT solution
=SUMPRODUCT(--(B7:B24=B24),--(LEFT(I7:I24)="C"),--(yourdaterange>=DATE(2005,3,1),--(yourdaterange<=DATE(2005,6,30))

Regards

Roger Govier