VanMn said:

I can see where my example might be confusing. I was going to use this method in other formulas as well, some formulas being index formulas and so on. But to your question Aladin, The sheet named "Data!", columns B and C are text columns, and columns G and H are numeric and referred to the most often, with column G being the most complete. Column A is the sort column and is a date format. As I said earlier, if practical I would use this concept in other formulas that referred to this data, not just the Sum Product ones. Thanks for your responses and your time. This is something I can live with, but get interested about learning new techniques

First of all, two definitions:

Activate Insert|Name|Define.

Enter

**BigNum** in the Names in Workbook box.

Enter the following in the Refers to box:

9.99999999999999E+307

Click Add.

Enter

**Lrow** in the Names in Workbook box.

Enter the following in the Refers to box:

=MATCH(BigNum,Data!$G:$G)

Click OK.

If you'd like to have a name, say DTable, that would cover all of your data, you just need to enter in the Refers to box:

=Data!$A$5:INDEX(Data$ET:$ET,Lrow)

__Intermezzo__: This is one use of Lrow. Another would be for example:

[1]

=MAX(Data!$A$5:INDEX(Data!$A:$A,Lrow))

How can we access a particular column of DTable?

INDEX(DTable,0,1)

would give you the current used range in column A on Data. So:

[2]

=MAX(INDEX(DTable,0,1))

is identical to [1].

**Efficiency issue**:

SumProduct formulas (and control+shift+entered (so-called array/matrix formulas) are expensive (cause performance degradation) if used in lots of cells and applied to huge ranges (of e.g., 25,000 cells).

What are the possible remedies:

(a) Switch to other means of processing like pivot tables, formulas with database functions;

(b) Apply such formulas to relevant subranges within the range of interest. This requires that the data is sorted in ascending order on some relevant field/attribute. You stated that your data is sorted on column A which houses dates. If SumProduct formulas include date conditions, you can make such formulas to apply to subranges by computing the subrange in relation to the date range. As is clear, this is something you should exploit.

(c) Switch from SumProduct/Array formula to SumIf formulas by creating an additional column(s) using concatenation.

If you had a formula like:

=SUMPRODUCT(--(Data!$C5:$C25000=B3),(Data!$B5:$B25000=B2),Data!$G5:$G25000)

used in lots of cells, you can concatenate C and B values in column EU using

=C5&CHAR(127)&B5

and then use:

=SUMIF(Data!EU:EU,B3&CHAR(127)&B2,Data!G:G)

or expand the definition of DTable with column EU, then use:

=SUMIF(INDEX(DTable,0,151),B3&CHAR(127)&B2,INDEX(DTable,0,7))