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?

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


JDavis wrote:
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?

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" wrote:

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


JDavis wrote:
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?

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


JDavis wrote:
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" wrote:


Hi

Try the array formula
{=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B 24))}
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


JDavis wrote:

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?

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" wrote:

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


JDavis wrote:
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" wrote:


Hi

Try the array formula
{=COUNT(IF((B7:B24=$B24)*(LEFT(I7:I24)="C"),B7:B 24))}
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


JDavis wrote:

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?

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")*(yourd aterange=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


JDavis wrote:
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" wrote:


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


JDavis wrote:

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" wrote:



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


JDavis wrote:


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?

Thanks Roger!

"Roger Govier" wrote:

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")*(yourd aterange=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


JDavis wrote:
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" wrote:


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


JDavis wrote:

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" wrote:



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


JDavis wrote:


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?