Using wild card characters in array formulas

I'm using an array formula to count occurrences of certain text values in a
column. The column contains various 2-letter codes, and I want to count the
number of occurrences of codes starting with "L". I've tried using a wild
card character in the formula ("L?"), but it doesn't work. THis approach
works fine for regular formulas, but I think there's something to do with
Array formulas that prevents it from counting what I want.

Any suggestions out there?
--
PJB

Posting your formula helps. But I counted the number of
occurrences staring with "L" using:

=COUNTIF(A:A,"L*")

It's not an array formula. If the formula needs to be case-
sensitive, then try:

=SUMPRODUCT(--EXACT(LEFT(A1:A10),"L"))

Not an array formula either.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I'm using an array formula to count occurrences of
certain text values in a
>column. The column contains various 2-letter codes, and
I want to count the
>number of occurrences of codes starting with "L". I've
tried using a wild
>card character in the formula ("L?"), but it doesn't
work. THis approach
>works fine for regular formulas, but I think there's
something to do with
>Array formulas that prevents it from counting what I want.
>
>Any suggestions out there?
>--
>PJB
>.
>

That syntax isn't working in my formula. Here's what I'm typing:

{=SUM(IF('OPS REPORT AM'!$D$3:$D$1000>"7",IF('OPS REPORT
AM'!$F$3:$F$1000="L?",1,0)))}

The formula checks column D for values greater than 7. When if finds one,
it checks the corresponding value in column F, looking for any 2-letter code
that begins with L.

If I replace the "L?" with "LA" for example, it counts all occurrences of
"LA". However, with the wild card, it is returning zero. I'v tried L* as
well, but no change. There are about 10 different codes that will fit the
criteria, hence my desire to use a wild card.

Appreciate any help.

"Jason Morin" wrote:

> Posting your formula helps. But I counted the number of
> occurrences staring with "L" using:
>
> =COUNTIF(A:A,"L*")
>
> It's not an array formula. If the formula needs to be case-
> sensitive, then try:
>
> =SUMPRODUCT(--EXACT(LEFT(A1:A10),"L"))
>
> Not an array formula either.
>
> HTH
> Jason
> Atlanta, GA
>
> >-----Original Message-----
> >I'm using an array formula to count occurrences of
> certain text values in a
> >column. The column contains various 2-letter codes, and
> I want to count the
> >number of occurrences of codes starting with "L". I've
> tried using a wild
> >card character in the formula ("L?"), but it doesn't
> work. THis approach
> >works fine for regular formulas, but I think there's
> something to do with
> >Array formulas that prevents it from counting what I want.
> >
> >Any suggestions out there?
> >--
> >PJB
> >.
> >
>

Try:

=SUM(('OPS REPORT AM'!$D$3:$D$1000>7)*(LEFT('OPS REPORT
AM'!$F$3:$F$1000)="L")*(LEN('OPS REPORT AM'!$F$3:$F$1000)
=2))

Array-entered. There's not real need to use IFs here.
Also, I changed "7" to 7...otherwise it won't work unless
the values in col. D really are text.

HTH
Jason
Atlanta, GA

>-----Original Message-----
>That syntax isn't working in my formula. Here's what
I'm typing:
>
>{=SUM(IF('OPS REPORT AM'!$D$3:$D$1000>"7",IF('OPS REPORT
>AM'!$F$3:$F$1000="L?",1,0)))}
>
>The formula checks column D for values greater than 7.
When if finds one,
>it checks the corresponding value in column F, looking
for any 2-letter code
>that begins with L.
>
>If I replace the "L?" with "LA" for example, it counts
all occurrences of
>"LA". However, with the wild card, it is returning
zero. I'v tried L* as
>well, but no change. There are about 10 different codes
that will fit the
>criteria, hence my desire to use a wild card.
>
>Appreciate any help.
>
>"Jason Morin" wrote:
>
>> Posting your formula helps. But I counted the number
of
>> occurrences staring with "L" using:
>>
>> =COUNTIF(A:A,"L*")
>>
>> It's not an array formula. If the formula needs to be
case-
>> sensitive, then try:
>>
>> =SUMPRODUCT(--EXACT(LEFT(A1:A10),"L"))
>>
>> Not an array formula either.
>>
>> HTH
>> Jason
>> Atlanta, GA
>>
>> >-----Original Message-----
>> >I'm using an array formula to count occurrences of
>> certain text values in a
>> >column. The column contains various 2-letter codes,
and
>> I want to count the
>> >number of occurrences of codes starting with "L".
I've
>> tried using a wild
>> >card character in the formula ("L?"), but it doesn't
>> work. THis approach
>> >works fine for regular formulas, but I think there's
>> something to do with
>> >Array formulas that prevents it from counting what I
want.
>> >
>> >Any suggestions out there?
>> >--
>> >PJB
>> >.
>> >
>>
>.
>