Is it possible to use wildcard characters in array formulas?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

TIA for your help
non working example: =sum(--(rng="XYZ"&"*") to find all cells in rng containing text begining with "XYZ"
Is there a way?
Thanks again
 
David,

Array enter (with Ctrl-Shift-Enter)

=SUM(--(LEFT(rng,3)="XYZ"))

HTH,
Bernie
MS Excel MVP

David said:
TIA for your help
non working example: =sum(--(rng="XYZ"&"*") to find all cells in rng
Click to expand...
containing text begining with "XYZ"
 
David

If you mean the number of cells here's one way:

=COUNTIF(rng, "xyz*")

or, if the text beginning with "XYZ" is not the
first word:

=COUNTIF(rng, "*xyz*")


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

David said:
TIA for your help
non working example: =sum(--(rng="XYZ"&"*") to find all cells in rng
Click to expand...
containing text begining with "XYZ"
 
Hi David,

To count cells containing text beginning with "XYZ", try

=SUMPRODUCT(--(LEFT(Range,3)="XYZ"))

Hope this helps!
 
Thanks Bernie for your solution
I am still wondering if the use of wildcards is possible in array functions? ....
 
Leo, thanks for your solution
Maybe I was trying to be too clever simplifying the problem.
Let me try again:
Lets say there is a Excel database table 2 fields x many records and I wish to count the records having "OK" in field 1 and having text begining with "XYZ" in field 2.
non working example: =sum((rng1="OK")*(rng2="XYZ*)). I know that there are other ways of doing this but I am interested in wether or not wildcard characters can be used (as in your countif example)
Thanks again
 
David,

No. Wildcard characters are not supported in array formulas.

HTH,
Bernie
MS Excel MVP
I am still wondering if the use of wildcards is possible in array
Click to expand...
functions? ....
 
Hi David
SUMPRODUCT does not accept this kind of wildcards. in this case you
could use FIND/SEARCH within the SUMPRODUCT formula to achieve a
similar result (or using LEFT in your specific case)
 
You're welcome, David.
Here's one way:

With cell references

=SUMPRODUCT((C2:C16="OK")*(COUNTIF(OFFSET(
D2,ROW(D2:D16)-ROW(D2),0),"xyz*")))

or, with named ranges

=SUMPRODUCT((rng1="OK")*(COUNTIF(OFFSET(
rng2,ROW(rng2)-MIN(ROW(rng2)),0,1),"xyz*")))

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

David said:
Leo, thanks for your solution
Maybe I was trying to be too clever simplifying the problem.
Let me try again:
Lets say there is a Excel database table 2 fields x many records and I
Click to expand...
wish to count the records having "OK" in field 1 and having text begining
with "XYZ" in field 2.
non working example: =sum((rng1="OK")*(rng2="XYZ*)). I know that there are
Click to expand...
other ways of doing this but I am interested in wether or not wildcard
characters can be used (as in your countif example)
 
...
...
With cell references

=SUMPRODUCT((C2:C16="OK")*(COUNTIF(OFFSET(
D2,ROW(D2:D16)-ROW(D2),0),"xyz*")))
Click to expand...

Fine but unclear whether it's more readable than either

=SUMPRODUCT((C2:C16="OK")*(LEFT(D2:D16,3)="xyz"))

or

=SUMPRODUCT((C2:C16="OK")*ISNUMBER(SEARCH("xyz*",D2:D16)))
or, with named ranges

=SUMPRODUCT((rng1="OK")*(COUNTIF(OFFSET(
rng2,ROW(rng2)-MIN(ROW(rng2)),0,1),"xyz*")))
Click to expand...

The MIN(...) was clever, but without checks that rng1 and rng2 are conformant
shapes/sizes the formula isn't robust. Also, MIN(ROW(PossiblyMulticellRange)) is
always inferior to CELL("Row",PossiblyMulticellRange).
 
Harlan Grove said:
...
..

Fine but unclear whether it's more readable than either

=SUMPRODUCT((C2:C16="OK")*(LEFT(D2:D16,3)="xyz"))
Click to expand...

Not relevant. The OP asked for a solution with wildcards.

or

=SUMPRODUCT((C2:C16="OK")*ISNUMBER(SEARCH("xyz*",D2:D16)))


The MIN(...) was clever, but without checks that rng1 and rng2 are conformant
shapes/sizes the formula isn't robust.
Click to expand...

So what you're saying is, that named ranges shouldn't be used in
array formulae (formulae at all)?

Also, MIN(ROW(PossiblyMulticellRange)) is
always inferior to CELL("Row",PossiblyMulticellRange).
Click to expand...

Why is that?

LeoH
 
...
...
So what you're saying is, that named ranges shouldn't be used in
array formulae (formulae at all)?
Click to expand...

No. I suppose getting error values returned is sufficient error trapping.
Why is that?
Click to expand...

You mean aside requiring one more function call and in and of itself needing
array entry? One drawback is internationalization if Excel doesn't translate the
"Row" argument. The degree to which that's relevant in an English language
newsgroup is arguable.
 
[...]
No. I suppose getting error values returned is sufficient error
trapping.


You mean aside requiring one more function call and in and of itself
needing array entry? One drawback is internationalization if Excel
doesn't translate the "Row" argument. The degree to which that's
relevant in an English language newsgroup is arguable.
Click to expand...

Hi Harlan
just as information: At least the German version is fine with a formula
like
=ZELLE("row",rng)

Interesting, isn't it. So Microsoft was able to deal for the CELL
function with the english values as first parameter. So I could either
enter
=ZELLE("row",rng)
or
=ZELLE("Zeile",rng)

both will work. Not sure about this behaviour in other localized
versions but I would assume that this is valid for them too
Frank
 
Harlan Grove said:
...
..

No. I suppose getting error values returned is sufficient error trapping.
Click to expand...

I'm probably being dense here, but would you please go into details about,
why you don't consider the formula robust?
You mean aside requiring one more function call and in and of itself
needing array entry?
Click to expand...

Ah yes, one more call.
I'm not familiar with the expression "in and of itself". Are you saying,
that
MIN(ROW(PossiblyMulticellRange)) and CELL("Row",PossiblyMulticellRange)
do not always return the same result, array entered or not?

LeoH
 
Hi Frank

Yes, that's interesting!
"Help" only mentions the localized values, and I have
never thought of trying the English ones.
Nice to know. Thanks for that piece of info.
The function also returns the English values for
e.g. "Type": "b", "l" and "v", while "Help" claims, that
the reurned values are localized, i.e. "t", "e" and "v".

LeoH


Frank Kabel said:
[...]
No. I suppose getting error values returned is sufficient error
trapping.


You mean aside requiring one more function call and in and of itself
needing array entry? One drawback is internationalization if Excel
doesn't translate the "Row" argument. The degree to which that's
relevant in an English language newsgroup is arguable.
Click to expand...

Hi Harlan
just as information: At least the German version is fine with a formula
like
=ZELLE("row",rng)

Interesting, isn't it. So Microsoft was able to deal for the CELL
function with the english values as first parameter. So I could either
enter
=ZELLE("row",rng)
or
=ZELLE("Zeile",rng)

both will work. Not sure about this behaviour in other localized
versions but I would assume that this is valid for them too
Frank
Click to expand...
 
Leo Heuser said:
Ah yes, one more call. . . . Are you saying, that
MIN(ROW(PossiblyMulticellRange)) and
CELL("Row",PossiblyMulticellRange)
do not always return the same result, array entered or not?
Click to expand...

They return the same results. The one fewer call isn't so much about recalc
speed as it is avoiding the 7 nested call limit. Fewer nested calls always
provides more flexibility.
 
Harlan Grove said:
They return the same results.
Click to expand...


You mentioned 2 points, that in your opinion made CELL() superior to the
MIN() solution. It's the second point "and in and of itself needing array
entry",
that I don't understand, and your answer above leaves me no wiser.


The one fewer call isn't so much about recalc
speed as it is avoiding the 7 nested call limit. Fewer nested calls always
provides more flexibility.
Click to expand...

Yes, I'm aware of that. I just forgot it at the moment. My "Ah yes" was
supposed to mean that.

Apparently you missed my question:
"I'm probably being dense here, but would you please go into details about,
why you don't consider the formula robust?"


LeoH
 
Hi Leo
my German Excel help (Excel 2003) states this correctly (shows that the
english characters are returned). So Excel behaves as describes as in
the Excel help (maybe they have corrected this in the newer help
version).

--
Regards
Frank Kabel
Frankfurt, Germany


Leo said:
Hi Frank

Yes, that's interesting!
"Help" only mentions the localized values, and I have
never thought of trying the English ones.
Nice to know. Thanks for that piece of info.
The function also returns the English values for
e.g. "Type": "b", "l" and "v", while "Help" claims, that
the reurned values are localized, i.e. "t", "e" and "v".

LeoH


Frank Kabel said:
[...]
No. I suppose getting error values returned is sufficient error
trapping.

Also, MIN(ROW(PossiblyMulticellRange)) is
always inferior to CELL("Row",PossiblyMulticellRange).

Why is that?

You mean aside requiring one more function call and in and of itself
needing array entry? One drawback is internationalization if Excel
doesn't translate the "Row" argument. The degree to which that's
relevant in an English language newsgroup is arguable.
Click to expand...

Hi Harlan
just as information: At least the German version is fine with a
formula like
=ZELLE("row",rng)

Interesting, isn't it. So Microsoft was able to deal for the CELL
function with the english values as first parameter. So I could
either enter
=ZELLE("row",rng)
or
=ZELLE("Zeile",rng)

both will work. Not sure about this behaviour in other localized
versions but I would assume that this is valid for them too
Frank
Click to expand...
Click to expand...
 
...
...
You mentioned 2 points, that in your opinion made CELL() superior to the
MIN() solution. It's the second point "and in and of itself needing array
entry", that I don't understand, and your answer above leaves me no wiser.
Click to expand...

I got that wrong. It appears MIN(ROW(range)) doesn't require array entry.
Apparently you missed my question:
"I'm probably being dense here, but would you please go into details about,
why you don't consider the formula robust?"
Click to expand...

No examples I can give, just gut feeling. Arrays of ranges, as can be produced
by passing INDIRECT an array 1st arg or OFFSET array 2nd or 3rd args, can be
dangerous things. I've crashed Excel a few times selecting such expressions in
the formula bar and pressing [F9], but that's a digression. Mixing arrays of
ranges with other arrays probably works almost all the time. My comments on
robustness were likely wrong, but

COUNTIF(OFFSET(rng2,ROW(rng2)-MIN(ROW(rng2)),0,1,1),"*foo*")

still strikes me as more than inelegant compared to

ISNUMBER(SEARCH("*foo*",rng2))

which also provides wildcards.
 
Back
Top