Is it possible to use wildcard characters in array formulas?

"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
..
With cell references

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

Fine but unclear whether it's more readable than either

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

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



or

=SUMPRODUCT((C2:C16="OK")*ISNUMBER(SEARCH("xyz*",D 216)))

or, with named ranges

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

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

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).

Why is that?

LeoH

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

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

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

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.

--
To top-post is human, to bottom-post and snip is sublime.

[...]
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.

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" skrev i en meddelelse
...
"Leo Heuser" wrote...
..
=SUMPRODUCT((rng1="OK")*(COUNTIF(OFFSET(
rng2,ROW(rng2)-MIN(ROW(rng2)),0,1),"xyz*")))

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

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

No. I suppose getting error values returned is sufficient error trapping.

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


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?

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" skrev i en meddelelse
...
[...]
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.

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

"Leo Heuser" wrote...
....
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?

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" skrev i en meddelelse
...
"Leo Heuser" wrote...
...
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?

They return the same results.


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.

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 Heuser wrote:
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" skrev i en meddelelse
...
[...]
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.

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

"Leo Heuser" wrote...
...
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.

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?"

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.

--
To top-post is human, to bottom-post and snip is sublime.

thanks Domenic,
I have thoroughly enjoyed all posts attached to this OP
--
David


"Domenic" wrote:

Hi David,

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

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

Hope this helps!

In article ,
"David" wrote:

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