Hi Ron

Thanks for your feedback.

What I'm trying to avoid here is the explicit use of a defined range.

I could simply specify =sum(A1:A65535). This method, however, takes a LONG
time to recalculate as one, the formula being used is somewhat more lengthy,
and two, it's repeated about 50 times on a summary page, each time drawing
different bits of information.

In effect, I'm trying to reduce the reculculation times by rather using a
dynamic range instead of a hard coded range.

I've found a workaround by making sure that there is a column that contains
data, and making sure that it extends down as far as the data entered. (This
happens to be date column, but it makes no difference to the outcome - it's
simply a range check at the end of the day).

Still, since I'm entirely AROC (Anal Retentive Obsessive Compulsive) the
question will still bug me as to how I might accomplish this WITHOUT the use
of a check column.

In case you're very bored/interested, here's the actual formula I'm using -
it may just shed some more light.

=SUM(IF((OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$ A))=Sheet2!$C$2)*(OFFSET(Sheet1!$B$2,0,0,COUNTA(Sh eet1!$A:$A))=Sheet2!$C$3)*(OFFSET(Sheet1!$C$2,0,0, COUNTA(Sheet1!$A:$A))=Sheet2!$C$4)*(OFFSET(Sheet1! $D$2,0,0,COUNTA(Sheet1!$A:$A))=Sheet2!$C$5)*((OFFS ET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$A:$A))=B10)+(OFF SET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$A:$A))=B10)),OF FSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$A:$A))))


Once again, thank you very much for taking the time to respond to my question.

Regards,

Denham.


"Ron Coderre" wrote:

Try one of these:

=SUM(A1:INDEX(A:A,MATCH(2,1/(1-ISBLANK(A1:A65535)))))
Note: that is an ARRAY FORMULA and must be committed by holding down [ctrl]
and [shift] when you press [enter]

OR
=SUM(OFFSET(A1,,,SUMPRODUCT(MAX((ROW(A1:A10000))*( A1:A10000<"")))))
Note: that formula contains OFFSET(), which is a volatile function and my
impact recalc performance in complex workbooks. It is a Non-array formula
(just press [enter])

OR
=SUM(A1:INDEX(A:A,SUMPRODUCT(MAX((ROW(A1:A10000))* (A1:A10000<"")))))
(Non-array, no volatile functions)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP