T
Tim Gahnstroem
Hi
As far as I have been able to figure out it is not possible to
reference whole columns in arrayformulas. Not that it is a big deal
but I would like to know why it is this way. It creates long and ugly
formulas, one simple example could look like this:
{=SUM('Main sheet'!I$1:I$3000=D$2)}
A much nicer and cleaner way would be:
{=SUM('Main sheet'!I:I=D$2)}
The first way also have the disadvantage of introducing anarbitary
limit that might, in the future, be accidently overrun without anyone
noticing unless special precausion is taken.
The way it is written now also indicates to me that all 3000 cells are
looked at even if the last 2900 is empty. Maybe I am wrong? With the
second case one would assume that Excel kept special notice of the
empty cells and not spent time looking at them. I guess there is a
reasonable explenation but I haven't been able to figure it out on my
own.
This particular formula could probably be written better in another
way, but it is just used to illustrate the concept.
A similar question would be why COUNT doesn't work as expected, why
introduce the sum and multiplication with one? It sure is not an good
looking solution. It would be interesting to hear any thoughts on the
issue.
{=SUM(('Main sheet'!I$1:I$3000=D$2)*1)}
Should be
{=COUNT('Main sheet'!I:I=D$2)}
Tim
As far as I have been able to figure out it is not possible to
reference whole columns in arrayformulas. Not that it is a big deal
but I would like to know why it is this way. It creates long and ugly
formulas, one simple example could look like this:
{=SUM('Main sheet'!I$1:I$3000=D$2)}
A much nicer and cleaner way would be:
{=SUM('Main sheet'!I:I=D$2)}
The first way also have the disadvantage of introducing anarbitary
limit that might, in the future, be accidently overrun without anyone
noticing unless special precausion is taken.
The way it is written now also indicates to me that all 3000 cells are
looked at even if the last 2900 is empty. Maybe I am wrong? With the
second case one would assume that Excel kept special notice of the
empty cells and not spent time looking at them. I guess there is a
reasonable explenation but I haven't been able to figure it out on my
own.
This particular formula could probably be written better in another
way, but it is just used to illustrate the concept.
A similar question would be why COUNT doesn't work as expected, why
introduce the sum and multiplication with one? It sure is not an good
looking solution. It would be interesting to hear any thoughts on the
issue.
{=SUM(('Main sheet'!I$1:I$3000=D$2)*1)}
Should be
{=COUNT('Main sheet'!I:I=D$2)}
Tim