Sum fields in table1 based on a definition of that sum in table2

C

chris3131

New Member
Joined
May 19, 2015
Messages
9
I have a table of data similar to this (lets call it "table1"), containing it's own row and column references (in red - ie. different from the Excel col & row references) and also containing subtotal rows and subtotal columns eg:
BCDEFG
desc1
desc2
desc3
subtotal (sum of rows 1-3)
desc4
desc5
subtotal (sum of rows 5-6)
grand total (sum of rows 4,7)

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]d[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]desc1[/TD]
[TD="align: center"]desc2[/TD]
[TD="align: center"]desc3[/TD]
[TD="align: center"]Total (sum of cols a-c)[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]
[TD="align: center"]?[/TD]

</tbody>
Sheet1​



For various reasons I don't want to simply include sum formulae within the table (@locations of '?') to complete the subtotal rows and columns. I need to do this calculation in a seperate table on a seperate worksheet, based on the specific row/col references from table1; I will then lookup those values to bring them back into "table1".

I was thinking "table2" would look something like this, whereby the field requiring the formula (? in table1) is defined as the 'Table Ref' and this is followed by a definition of what the sum formulae needs to do. eg. datapoint a4 needs to give the sum of a1:a3; datapoint d3 needs to give the sum of a3:d3; and in the case of datapoint a8 this needs to sum a4 and a7, although in this case (all rows in yellow) I don't mind if these are on multiple rows since I can use a SUMIF to bring them back into table1.
BCDEFGH

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: center"]Table Ref[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Calculation definition[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Cols[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Rows[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]Col[/TD]
[TD="align: center"]Row[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"]from[/TD]
[TD="align: center"]to[/TD]
[TD="align: center"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]15[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]18[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]25[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]27[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]12[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]23[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]15[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]25[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]18[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFF2CC]#FFF2CC[/URL] , align: center"]27[/TD]

[TD="align: center"]19[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]20[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]15[/TD]

[TD="align: center"]21[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]24[/TD]

[TD="align: center"]22[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]45[/TD]

[TD="align: center"]23[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]33[/TD]

[TD="align: center"]24[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]42[/TD]

[TD="align: center"]25[/TD]
[TD="align: center"]d[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]120[/TD]

</tbody>
Sheet2​



So my question is what is the formula I need to use in the Result column? I'm sure its a combination of INDEX, MATCH and INDIRECT but after hours of trying, I cant quite fathom it.
Also remember that table1 is on a seperate worksheet to table2 so this needs to be taken into account in the formula. I am also very happy to make use of range names in table1 if that makes the formula more managable.

If you have a better suggestion for how table2 could be presented then I'm very happy to change that. What is key is:
a) that I have a reference to the datapoint in table1
b) that the calculation definition can be defined by the user in a logical fashion
and c) that the result of the formula is presented alongside the datapoint reference in such a way that is can be brought back into table1 as a total.

many thanks in advance
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
E
  • Question Question
VBA code clean up
Replies
9
Views
593
mumps
mumps
L
  • Question Question
Sending Email with Table in Body
Replies
3
Views
781
Domenic
D
D
  • Solved
Counting formula results and text data while ignoring blank cells
2
Replies
16
Views
812
Darren_workforce
D
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
160
Cubist
Cubist

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top