Placeholfer in Arraz or Sumproduct

[Haui]

using: Excel2003

why do placeholders work in e.g. sumif, but not in arrays or sumproduct?

see the following example:

<TABLE style="WIDTH: 554pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=738 border=0 x:str><COLGROUP><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2730" span=2 width=128><COL style="WIDTH: 266pt; mso-width-source: userset; mso-width-alt: 7552" width=354><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2730" width=128><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=128 height=17></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=128></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 266pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=354></TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=128>Result</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>buro</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>1</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0" x:fmla='=SUM(IF((A2:A4="buro*"),B2:B4))' x:arrayrange="C2">{=SUM(IF((A23:A25="buro*"),B23:B25))}</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>0</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>buro2</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>2</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="6" x:fmla='=SUMIF(A2:A4,"buro*",B2:B4)'>=SUMIF(A23:A25,"buro*",B23:B25)</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>buro3</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>3</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num="0" x:fmla='=SUMPRODUCT(--(A2:A4="buro*"),B2:B4)'>=SUMPRODUCT(--(A23:A25="buro*"),B23:B25)</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>0</TD></TR></TBODY></TABLE>

in what way would the formulas have to be adjusted?

regards

 

[Stormseed]

What are you trying to do ? Could you provide a complete example or atleast explain what you want to accomplish alongwith your expected results ?

 

[barry houdini]

Strictly speaking it's actually the comparison operator ( = in this case) that doesn't allow wildcards. You can use LEFT, e.g.

=SUMPRODUCT(--(LEFT(A23:A25,4)="buro"),B23:B25)

or if buro could appear anywhere in the cell

=SUMPRODUCT(--ISNUMBER(SEARCH("buro",A23:A25)),B23:B25)

 

[Haui]

sorry for the lack of information Stormseed

barry houdini: search works just as desired, thx a lot

regards