Adding rows based on cloumn and row values

[wiltera]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi there

I am struggling with the following problem. I need to add a series of cells based on values of columns and rows in the form of a matrix as follows:

Column values
Row values 1 2 3 4 5
           1 0 4 0 8 10
           2 1 3 5 7 9
           3 5 5 6 6 8
           4 0 0 0 8 2
           5 2 4 6 8 9

Now if the row value is say 3 (read across) and the column value is 5 (read down), I want excel to add the corresponding cells 2+4+6. If the row value is 5 and the column value is 5 I want excel to add 2+4+6+8+9.

I have been playing with sumif() and sum(if()) etc but I have had no luck. Please could you help.

Thanks and regards

 

[Bob Greenblatt]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi
there

I am struggling with the following problem. I need to add a series of cells
based on values of columns and rows in the form of a matrix as follows:

Column values
Row values 1 2 3 4 5
1 0 4 0 8 10
2 1 3 5 7 9
3 5 5 6 6 8
4 0 0 0 8 2
5 2 4 6 8 9

Now if the row value is say 3 (read across) and the column value is 5 (read
down), I want excel to add the corresponding cells 2+4+6. If the row value is
5 and the column value is 5 I want excel to add 2+4+6+8+9.

I have been playing with sumif() and sum(if()) etc but I have had no luck.
Please could you help.

Thanks and regards

I can¹t figure out what you are trying to do based on your example. I think
you have specified either the row or column values incorrectly in the
example above. However, look into the Offset function. When combined with
sum, you will get what you want. For example, if the upper left corner is in
cell A1, then something like this will work:
=sum(offset($A$1,row value minus 1,0,1,number of columns ­1))

 

[wiltera]

Bob

I have sent you a mail with an excel spreadsheet attached. Hopefully this will make things clearer for you. Thanks for your help.

Rgds

Anthony