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

[JDavis]

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


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?

[JDavis]

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?
>

[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


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?
>>

[JDavis]

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?
> >>
>

[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


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?
>>>>

[JDavis]

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")*(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
>
>
> 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?
> >>>>
>