Sum data based on coordinates

chris3131

New Member
Joined
May 19, 2015
Messages
9
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a
[/TD]
[TD]b
[/TD]
[TD]c
[/TD]
[TD]d
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]100
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10
[/TD]
[TD]4
[/TD]
[TD]14
[/TD]
[TD]200
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]15
[/TD]
[TD]6
[/TD]
[TD]21
[/TD]
[TD]300
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]28
[/TD]
[TD]400
[/TD]
[/TR]
</tbody>[/TABLE]

If I have a table like the one above with column and row headers...NB. these are not the Excel headings, they are specific to the table and could appear anywhere on a worksheet.
I want to be able to specify coordinates (single, multiple, rows and columns) and have a consistent formula return the sum.
eg.
b3: formula returns: 6
b3,c2: formula returns: 20
d1:d3: formula returns 600
a2:d2: formula returns: 228

I don't mind how the coordinates have to be written in order to get the appropriate formula to work.eg. the row/column coordinate can be in a single cell, or in separate cells. However what is key is that the user is able to enter those coordinates in a consistent manner and have a consistent formula return the result.

Similar tables will be on multiple worksheets, so I would like the user to also be able to specify the worksheet and have the consistent formula return he value from the right table on the right worksheet. If range names can but used to reference the dataset/row headers/col headers then all the better.

Thanks in advance
 
Great solution Dante. But unfortunately I need a non-VBA way of doing this....if that’s possible??

See if this does what you need


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
C
[/TD]
[TD="bgcolor: #DCE6F1"]
D
[/TD]
[TD="bgcolor: #DCE6F1"]
E
[/TD]
[TD="bgcolor: #DCE6F1"]
F
[/TD]
[TD="bgcolor: #DCE6F1"]
G
[/TD]
[TD="bgcolor: #DCE6F1"]
H
[/TD]
[TD="bgcolor: #DCE6F1"]
I
[/TD]
[TD="bgcolor: #DCE6F1"]
J
[/TD]
[TD="bgcolor: #DCE6F1"]
K
[/TD]
[TD="bgcolor: #DCE6F1"]
L
[/TD]
[TD="bgcolor: #DCE6F1"]
M
[/TD]
[TD="bgcolor: #DCE6F1"]
N
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
a​
[/TD]
[TD]
b​
[/TD]
[TD]
c​
[/TD]
[TD]
d​
[/TD]
[TD]
Some​
[/TD]
[TD][/TD]
[TD]
Areas/Cell​
[/TD]
[TD]
LeftCellRow​
[/TD]
[TD]
LeftCellCol​
[/TD]
[TD]
RightCellRow​
[/TD]
[TD]
RightCellCol​
[/TD]
[TD]
Result​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
2
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
5​
[/TD]
[TD]
2​
[/TD]
[TD]
7​
[/TD]
[TD]
100​
[/TD]
[TD]
16​
[/TD]
[TD][/TD]
[TD]
1​
[/TD]
[TD]
1​
[/TD]
[TD]
b​
[/TD]
[TD]
1​
[/TD]
[TD]
b​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
3
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
10​
[/TD]
[TD]
4​
[/TD]
[TD]
14​
[/TD]
[TD]
200​
[/TD]
[TD]
45​
[/TD]
[TD][/TD]
[TD]
2​
[/TD]
[TD]
1​
[/TD]
[TD]
a​
[/TD]
[TD]
4​
[/TD]
[TD]
a​
[/TD]
[TD]
32​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
4
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
15​
[/TD]
[TD]
6​
[/TD]
[TD]
21​
[/TD]
[TD]
300​
[/TD]
[TD]
721​
[/TD]
[TD][/TD]
[TD]
3​
[/TD]
[TD]
2​
[/TD]
[TD]
c​
[/TD]
[TD]
2​
[/TD]
[TD]
some​
[/TD]
[TD]
259​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
5
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
2​
[/TD]
[TD]
8​
[/TD]
[TD]
28​
[/TD]
[TD]
400​
[/TD]
[TD]
48​
[/TD]
[TD][/TD]
[TD]
4​
[/TD]
[TD]
4​
[/TD]
[TD]
b​
[/TD]
[TD]
1​
[/TD]
[TD]
d​
[/TD]
[TD]
1090​
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
6
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD]
3​
[/TD]
[TD]
7​
[/TD]
[TD]
9​
[/TD]
[TD]
11​
[/TD]
[TD]
85​
[/TD]
[TD][/TD]
[TD]
5​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]
7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
TOTAL​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
1383​
[/TD]
[/TR]
</tbody>[/TABLE]


Formula in N2 copied down
=IF(OR(J2="",K2=""),"",SUM(INDEX($C$2:$G$6,MATCH(J2,$B$2:$B$6,0),MATCH(K2,$C$1:$G$1,0)):INDEX($C$2:$G$6,MATCH(L2,$B$2:$B$6,0),MATCH(M2,$C$1:$G$1,0))))

Formula in N7
=SUM(N2:N6)

Remark:
The user must enter the coordinates as follows
1. a single cell
Repeat the coordinates in columns J: K and L: M
2. An area
Insert the coordinates of the leftmost cell in columns J: K; insert the coordinates of the rightmost cell in columns L: M

Hope this helps

M.
 
Last edited:
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,224,944
Messages
6,181,928
Members
453,073
Latest member
bfrobin

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