variable cell references

[jjk98]

I have a large array (10x2200) of small integers. I have a second array of
three columns and about 200 rows. The first column of the second array has a
column number in the first big array (thus it has a value from 1 - 10) the
2nd and 3rd columns in the second array have a start and end row in the first
big array.

I would like to be able to compute the sum of the integers in the column
specified from the start row to the end row. For example if one of the rows
in the secord array has values 10 40 50 I want to compute the sum of rows 40
through 50 in column 10 of my big array.

I've tried the indirect function and the address function in various
combinations but cannot seem to get it right.

 

[Tom Hutchins]

Try

=SUM(INDIRECT("'Sheet1'!"&ADDRESS(B1,A1)&":"&ADDRESS(C1,A1)))

In this example, the large array begins in A1 of Sheet1, and the small array
is in columns A, B, and C on another sheet, beginning in row 1. The formula
above would be entered in D1 of the small array sheet and copied down.

Hope this helps,

Hutch

 

[RagDyer]

Say the big array is in A1 to J2200.

The small array is in X1 to Z200.

Enter this in say AA1, and copy down to AA200:

=SUM(INDEX($A$1:$J$2200,Y1,X1):INDEX($A$1:$J$2200,Z1,X1))

 

[RagDyer]

Should mention that if a cell is blank in the small array, the formula will
total the *entire* range of the missing cell, be it column or row.

 

[Ken Johnson]

I have a large array (10x2200) of small integers. I have a second array of
three columns and about 200 rows. The first column of the second array has a
column number in the first big array (thus it has a value from 1 - 10) the
2nd and 3rd columns in the second array have a start and end row in the first
big array.

I would like to be able to compute the sum of the integers in the column
specified from the start row to the end row. For example if one of the rows
in the secord array has values 10 40 50 I want to compute the sum of rows 40
through 50 in column 10 of my big array.

I've tried the indirect function and the address function in various
combinations but cannot seem to get it right.

Say the top left cell (not heading) of the large array is Sheet1!$A$2,
and the first row of values in the second array is Sheet2!$A$2:$B$2,
then

=SUM(OFFSET(Sheet1!$A$2,B2-1,A2-1,C2-B2+1,1))

in Sheet2!$D$2 filled down to the bottom of the second array seems to
work.

Ken Johnson