sum, match, offset help

[Michael J. Malinsky]

I have the following situation:

Sheet1:

A
1 5

Sheet2:

A B C
1 3 5 2
2 1 3 4
3 2 4 5
4 8 2
5 7

I want to put a formula in Sheet 1, B1 that will find that value typed into
Sheet1, A1 and return the sum of the values under that. In the above
example, since I have the number 5 in Sheet1, A1, the formula in Sheet1, B1
would return 7. As can be seen from the example, the length of the columns
vary.

Any help would be appreciated.

TIA.

 

[Michael J. Malinsky]

Clarification: I want the formula to Sheet 1, B1 to find that value typed
into Sheet1, A1 in Sheet2, Row 1, and return the sum of the values under
that.

Sorry.

 

[Frank Kabel]

Hi Michael
one way:
=SUM(OFFSET('sheet2'!$A$1,SUMPRODUCT(('sheet2'!A1:C100=A1)*(ROW('sheet2
'!A1:C4))),SUMPRODUCT(('sheet2'!A1:C100=A1)*(ROW('sheet2'!A1:C4)))-1,10
0))

I made the assumption, that below your matrix in sheet2 no data exist.
Otherwise you have to calculate the third parameter of the OFFSET
function (currently set to 100)

Frank

 

[Frank Kabel]

Hi Michael
my first provided solution will find any value in sheet2 (not only row
1)
If you only want to lookup A1 in row 1 of sheet2 try
=SUM(OFFSET('sheet2'!$A$1,1,MATCH(A1,'sheet2'!A1:C1,0)-1,100))

Frank

 

[Peo Sjoblom]

one way

=SUM(INDEX(MyTable,ROW(MyTable)-1,MATCH(A1,INDEX(MyTable,1,),0)))-A1

assuming that the table in sheet2 is called MyTable

 

[Michael J. Malinsky]

Frank,

Seems to work great. Thanks. The only thing I changed is that since the
number of values in any given column can change, I changed the third
parameter of the OFFSET to 65535 so that it will sum the entire column.

Thanks.

 

[Harlan Grove]

I have the following situation:

Sheet1:

A
1 5

Sheet2:

A B C
1 3 5 2
2 1 3 4
3 2 4 5
4 8 2
5 7

I want to put a formula in Sheet 1, B1 that will find that value typed into
Sheet1, A1 and return the sum of the values under that. In the above
example, since I have the number 5 in Sheet1, A1, the formula in Sheet1, B1
would return 7. As can be seen from the example, the length of the columns
vary.

Would there ever be anything below the table in Sheet2? If not, and if the ID
entered in Sheet1!A1 and all the entries in Sheet2!1:1 weer numbers, then
simplest would be

=SUM(OFFSET(Sheet2!$A:$A,0,MATCH(A1,Sheet2!$1:$1,0)-1))-A1

If there could be data below the top-left range in Sheet2 that shouldn't be
included, and the ID in Sheet1!A1 could be text as well as number, then try the
array formula

=SUM(OFFSET(Sheet2!$A$2,0,MATCH(A1,Sheet2!$1:$1,0)-1,MATCH(TRUE,
ISBLANK(OFFSET(Sheet2!$A$2:$A$65536,0,MATCH(A1,Sheet2!$1:$1,0)-1)),0),1))