# Sum data based on coordinates

#### chris3131

##### New Member
 a b c d 1 5 2 7 100 2 10 4 14 200 3 15 6 21 300 4 2 8 28 400

<tbody>
</tbody>

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

#### Special-K99

##### Well-known Member
They're not coordinates, they're cell references.

=SUM(INDIRECT(...)))

works for ranges
B3
D1:D3
A2:D2

but not for comma separated cell references.

Last edited:

#### chris3131

##### New Member
No....I think you misunderstand. As I said above, they are not the Excel cell references. This is a table within a worksheet which has it's own 'cell references' if you like. So the table I have included above might be anywhere in the worksheet but still have its own 'cell references of rows1-4 and cols a-d.

Alternatively if you could give me an example of a full formula, because I can't see how the SUM(INDIRECT(..)) would work. Thanks

#### Special-K99

##### Well-known Member
But you'd have to know the left hand coordinate of the "table" in order to offset x, y parameters that the users enters.

If you were just referring to absolute cell references you could have said

With "a" in B1
in A1 (the top left blank cell in your table) put B2:E3
in a blank cell put SUM(INDIRECT("A1"))

would sum the values in B2:E3 (row 2 columns a to d in your table) - producing 228.

Last edited:

#### chris3131

##### New Member

ADVERTISEMENT

It’s not quite the solution I am looking for, and apologies if I haven’t explained it properly.
In your example the user is specifying the excel cell references (B2:E3), when I want the user to specify the table ‘cell references’ (a2:d2) and have the formula reference these user specified table cell references.
I appreciate that the dataset (and/or the col/row headers) would need to be defined as well, preferably with the use of named ranges.

#### DanteAmor

##### Well-known Member
Try this udf.

Assume that the headings are in row 1, you can use any word. It also assumes that the column will be in column A and will always be numbers.
You can use coordinates separated by commas and also separated by ":"

Check the examples below.

Excel Workbook
ABCDEFGHI
1abcdSomeExamplesResult
2152710016b36
321041420045b3,c2,d4420
4315621300721b3,c2, d1:d3620
54282840048d1:d3600
653791185a2:d2228
7some585
8some2,some3766
9some3:some5854
10a1:some2403
Hoja4

Put the following code in a module

Code:
``````Option Explicit
Function Sum_Coordinates(coor As String)
Dim r1 As String, r2 As String, c As String, s As String, wcell As String
Dim dcomas As Variant, dpoint As Variant
Dim i As Long, j As Long, acum As Double

'separate by coma
dcomas = Split(coor, ",")

For i = 0 To UBound(dcomas)
'separate by points
c = WorksheetFunction.Trim(dcomas(i))

If InStr(1, c, ":") Then
dpoint = Split(c, ":")
r1 = ""
r2 = ""
For j = 0 To UBound(dpoint)
'separate x-y
s = WorksheetFunction.Trim(dpoint(j))
wcell = ad_Celda(s)
If r1 = "" Then
r1 = wcell
Else
r2 = wcell
acum = acum + WorksheetFunction.Sum(Range(r1 & ":" & r2))
End If
Next
Else
'separate x-y
s = WorksheetFunction.Trim(dcomas(i))
wcell = ad_Celda(s)
acum = acum + Range(wcell)
End If
Next
Sum_Coordinates = acum
End Function

Function ad_Celda(s)
Dim xs As String, ys As String, res As String
Dim k As Long, wCol As Long, wRow As Long
Dim b As Range

xs = ""
ys = ""
For k = 1 To Len(s)
If Mid(s, k, 1) Like "*[!0-9]*" Then
xs = xs & Mid(s, k, 1)
Else
ys = ys & Mid(s, k, 1)
End If
Next
'search coordinate initial
Set b = Rows(1).Find(xs)
If Not b Is Nothing Then
wCol = b.Column
Set b = Columns(1).Find(ys)
If Not b Is Nothing Then
wRow = b.Row
res = Cells(wRow, wCol).Address
Else
res = "Error. Column not found"
End If
Else
res = "Error. Header not found"
End If
ad_Celda = res
End Function``````

INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.

ADVERTISEMENT

Dante

Nice!

M

#### chris3131

##### New Member
Great solution Dante. But unfortunately I need a non-VBA way of doing this....if that’s possible??

#### DanteAmor

##### Well-known Member
Find exercises with Solve

Replies
4
Views
43
Replies
0
Views
55
Replies
1
Views
39
Replies
11
Views
123
Replies
3
Views
33

Threads
1,108,502
Messages
5,523,296
Members
409,508
Latest member
Afc