sum, match, offset help

  • Thread starter Thread starter Michael J. Malinsky
  • Start date Start date
M

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.
 
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.
 
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
 
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
 
one way

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

assuming that the table in sheet2 is called MyTable
 
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.
 
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.
Click to expand...

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))
 
Back
Top