MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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