Using wild cards in array formulas?

[Father Guido]

Hi,

How would I write a formula to use wildcards in an array formula?

Say I have two columns, one has values such as 2L90, 2L91, 2L92,
2L54, 2L55 etc. and I want to select all the 2L90/91/92, and add up
the numbers in the other column. I can never remember whether I need
sum, sumif, count, countif etc. But also how to do wildcards, if it's
even possible.

={sum((a1:a50,"2L9*")*(b1:b50))}


Thanks!

Norm

 

[Ken Wright]

Try the following:-

=SUMPRODUCT((LEFT(A1:A50,3)="2L9")*(B1:B50))

 

[Father Guido]

On Mon, 13 Sep 2004 22:28:07 +0100, "Ken Wright"

Æ’Try the following:-
Æ’
Æ’=SUMPRODUCT((LEFT(A1:A50,3)="2L9")*(B1:B50))

Thanks Ken, that works fine.
So I gather wildcards can't be used then?

 

[Ken Wright]

They can be used in various formulas but not all functions will support them,
but there is little point anyway if it is not needed. Another option that is
similar is to use FIND or SEARCH within a SUMPRODUCT function, which will mimic
a wildcard search.

=SUMPRODUCT(ISNUMBER(SEARCH("bcd",$A$2:$A$30))*$B$2:$B$30)

Other misc examples from these groups

=SUMIF(P25,"<>*LLC",DB2B5)
=COUNTIF(data,"SEC*")
=IF(COUNTIF(A2:A2,"?abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc"),"yes","no")
=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
=COUNTIF(A1:A5,"*a*")
=COUNTIF(A1:A5,"*a")
=COUNTIF(A1:A5,"a*")
=COUNTIF(A1:A5,"a?cd")
=AVERAGE(IF(ISNUMBER(SEARCH("green",A1:A10)),B1:B10))
=COUNTIF(A:A,"????t*")
=SUMPRODUCT((F3:F5000="1c33")*ISNUMBER(SEARCH("3w15?",I3:I5000)))
=VLOOKUP("*"&A1&"*",Sheet2!A:B,2, FALSE)

 

[Father Guido]

Thanks Ken,

It's interesting to see various other approaches to the same problem.

Norm
________________________________________________

On Tue, 14 Sep 2004 19:50:34 +0100, "Ken Wright"

Æ’They can be used in various formulas but not all functions will
support them,
Æ’but there is little point anyway if it is not needed. Another option
that is
Æ’similar is to use FIND or SEARCH within a SUMPRODUCT function, which
will mimic
Æ’a wildcard search.
Æ’
Æ’=SUMPRODUCT(ISNUMBER(SEARCH("bcd",$A$2:$A$30))*$B$2:$B$30)
Æ’
Æ’Other misc examples from these groups
Æ’
Æ’=SUMIF(P25,"<>*LLC",DB2B5)
Æ’=COUNTIF(data,"SEC*")
Æ’=IF(COUNTIF(A2:A2,"?abc"),"yes","no")
Æ’=IF(COUNTIF(A2:A2,"*abc"),"yes","no")
Æ’=IF(COUNTIF(A2:A2,"*abc*"),"yes","no")
Æ’=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
Æ’=IF(ISNUMBER(FIND("abc",A2)),"yes","no")
Æ’=COUNTIF(A1:A5,"*a*")
Æ’=COUNTIF(A1:A5,"*a")
Æ’=COUNTIF(A1:A5,"a*")
Æ’=COUNTIF(A1:A5,"a?cd")
Æ’=AVERAGE(IF(ISNUMBER(SEARCH("green",A1:A10)),B1:B10))
Æ’=COUNTIF(A:A,"????t*")
Æ’=SUMPRODUCT((F3:F5000="1c33")*ISNUMBER(SEARCH("3w15?",I3:I5000)))
Æ’=VLOOKUP("*"&A1&"*",Sheet2!A:B,2, FALSE)

 

[Ken Wright]

You're very welcome