Using wild card characters in array formulas

[PJB Shark]

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?

 

[Jason Morin]

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

 

[PJB Shark]

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.

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

 

[Jason Morin]

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.