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$10007)*(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
.


.