Array formula
Posted by Ian on July 16, 2001 7:39 AM
I'm having problems getting my head round this formula
{=SUM(B:B=$A$19)*(C:C=$E$10)}
where B:B must = a reference to a cell with a text string and likewise C:C. All I get is a #num error??
what am I doing wrong.
Also if i can get this to work, can then use across workbooks??
thanx Ian
Posted by Mark W. on July 16, 2001 7:54 AM
Ian, array formulas operate on array constants
such as {1;2;3} or on cell ranges. Now I know
this is gonna seem like "hair splitting", but
B:B isn't a cell range -- it's a reference to
all cells in column B. This distinction is
described in the Excel Help topic, "About cell
and range reference". A cell range is constructed
by "[entering] the reference for the cell in the
upper-left corner of the range, a colon (:), and
then the reference to the cell in the lower-right
corner of the range." B1:B10 is a valid cell
range.
Posted by Aladin Akyurek on July 16, 2001 7:57 AM
Array formulas do not accept whole columns like A:A as arguments. You should replace them by the true ranges like A1:A6000.
BTW, too big range makes an array formula less attractive qua performance.
Aladin
Posted by Ian on July 16, 2001 8:06 AM
Cheers guys, I was just being lazy, that's put an end to that.
Posted by Mark W. on July 16, 2001 8:12 AM
Also...
> can [I] get this to work, can then use across workbooks?
I assume you mean across worksheets. Such a reference
is a 3-D refence (e.g., Sheet1:Sheet2!A). The Help
topic for "Guidelines for using 3-D references" states,
"3-D references cannot be used in array formulas."
Posted by Ian on July 16, 2001 9:23 AM
It was from 1 book 2 another: but you 2 solving it answered that 1
Posted by Ian on July 16, 2001 9:42 AM
It was from 1 book 2 another: but you 2 solving it answered that 1
