Using a named range inside an array formula.....

...and another problem has been solved !!!

Thanks - I've tried this and it works fine - it was the bit about what functions like ROW() actually return that suddenly made sense.

Cheers !!

Andrew

Re: Problem using your solution further.......

As this array number is a different size to the other array definitions, I assume it's this which throws up the error ? Is there any other way to achieve the same thing inside that array formula ?

Thanks (hopefully) for any help you can all offer !

Re: Problem using your solution further.......

Andrew, looks like there are 2 things awry here,
1) are you trying to use R1C1 format ??
2) if not then the areas are not equal = error
3) yes you are correct in that the column function
is returning an array number.

Problem using your solution further.......

{=SUM((INDIRECT("D"&DataTop&":D"&DataBottom) > 1997) * (INDIRECT("E"&DataTop&":E"&DataBottom)="L") * (INDIRECT("R" & DataTop & "C" & COLUMN() & ":R" & DataBottom & "C" & COLUMN(),FALSE)))}

Is the return type of the column function the reason why this use of your idea doesn't work, or is it trying to combine A1 and R1C1 style within an array formula ??

I wanted to use this last part so that the formula can be copied across a *very* large number of columns and will automatically work relative to the column it's in.

Yours, hopefully {again}

Andrew Beeston

Re: Problem using your solution further.......

Andrew
All ranges must be the same size otherwise array formulas will not work.
I don't think you can have R1C1 references mixed in with absolute references. In any event the R1C1 part of your formula doesn't include the square brackets that are needed for such refs.
Also,I think that in the third criteria of your formula the bit that reads ",FALSE" should be "=FALSE".
There doesn't seem to be anything wrong with the first two criteria in the formula.
I've been playing around with the third criteria and have got as far as getting these formulas to work on their own :-

=COUNTIF(INDIRECT(CHAR(COLUMN()+64)&ROW() + DataTop&":"&CHAR(COLUMN()+64)&ROW() + DataBottom),FALSE)

=SUM(INDIRECT(CHAR(COLUMN()+64)&ROW() + DataTop&":"&CHAR(COLUMN()+64)&ROW() + DataBottom))

I've tried incorporating the following into your formula but can't get it to work (returns 0).

(INDIRECT(CHAR(COLUMN()+64)&ROW() + DataTop&":"&CHAR(COLUMN()+64)&ROW() + DataBottom)=FALSE)

Perhaps you might be able to use this concept.
The way it works is that the character codes for the capital letters A to Z are from 65 to 90. Because of this, "CHAR(COLUMN()+64)" returns the column letter of the cell that contains the formula (but only up to Z).
Hope this helps and you can get it working.
(But remember that all three ranges in the formula must be the same size).

Celia

Andrew

There are a number of ways to do this (differently)
But in your situation the Row function yields an
array and not a hard coded fig eg {7} and not 7
which is why the error occurs any of the address
ref functions will yield this.
To get around this you would have to take a few
steps extra.
In an unused cell put a formula that references
the Table_Top eg.
C7 = =ROW(Table_Top) which should = 7
NOW name this cell TOP
do the same with the bottom of your range.

So that your formula will now reference this hard
coded cell = 7 = Table_Top eg
=SUM((INDIRECT("D"&TOP&":D"&BOTTOM)>17) * (INDIRECT("E"&TOP&":E"&BOTTOM)="L") * (INDIRECT("J"&8&":J"&BOTTOM)))

HTH

Ivan

Follow-up

Andrew

I've just looked at your earlier message about needing to fill the formula across columns - I should have let that sink in before doing anything!
The way your formula is written, I think all of the cell references will be hard coded, so that it cannot be filled across columns.

Can you not use the formula in the following format ? :-

=SUM((INDIRECT("D"&DataTop&":D"&DataBottom) > 1997) * (INDIRECT("E"&DataTop&":E"&DataBottom)="L") * (F$10:F$20=FALSE))

You will, of course have to change manually the rows (or perhaps only the bottom row?) in the third criterion of the formula when the data row changes. Alternatively, you can set the bottom row lower than the last row you are likely to use, so that you do not have to keep changing the formula.
(As an aside, is it really necessary to assign a name to the top and bottom row numbers?)

It can also probably be done by using dynamic named ranges, but this would be a bit restrictive in that there must not be anything below the data range and there must be no blank cells in the data range.

It’s a bit difficult to work out the best way without knowing exactly what you are trying to do. If you like, you can send me a sample workbook.

Celia

Try this formula

Andrew
See if this does what you need :-

=SUM((INDIRECT("D"&DataTop&":D"&DataBottom) > 1997) * (INDIRECT("E"&DataTop&":E"&DataBottom)="L") * (COUNTIF(INDIRECT(CHAR(COLUMN()+64)&ROW() + DataTop&":"&CHAR(COLUMN()+64)&ROW() + DataBottom),FALSE)/(DataBottom-DataTop+1)))

Celia

Andrew
Alternatively, you could name each of the ranges in Columns D, E, & J and then use the following formula :-

=SUM((ColD>1997)*(ColE="L")*ColJ)

The named ranges must be the same size, but if you make them larger than you are likely to need, you will not have to keep re-defining them.

Celia

Correction

Sorry, I pasted the wrong formula. I've just checked the one I meant to paste and it doesn't work anyway.
I've posted a question about this on another message board - will let you know if I get a solution.
Celia

Re: Follow-up

Andrew

I've worked out a way of getting your formula to work but I suggest that you should look for a simpler alternative :-

I've assumed that the first column for the third criterion is column F and that the first row of data in column F is the same row number as the DataTop number.

Enter the following formula in F1 :-
=(ADDRESS(ROW()+DataTop-1,COLUMN(),2) & ":" & ADDRESS(ROW() + DataBottom - 1,COLUMN(),2))
Fill this formula across the appropriate columns.

Create a name called Rng (or whatever) and type into the RefersTo box :-
=EVALUATE(Sheet1!F$1)

Your formula will then be :-
=SUM((INDIRECT("D"&DataTop&":D"&DataBottom) > 1997) * (INDIRECT("E"&DataTop&":E"&DataBottom)="L") * (Rng=FALSE))

Not being able to get the formula to work was irritating the Hell out of me! But anyway. there has to be a simpler way than this.

Celia