Sum data based on coordinates

chris3131

New Member
Joined
May 19, 2015
Messages
9
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
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,348
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
Joined
May 19, 2015
Messages
9
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
Joined
Nov 7, 2006
Messages
8,348
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
Joined
May 19, 2015
Messages
9

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
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

chris3131

New Member
Joined
May 19, 2015
Messages
9
Great solution Dante. But unfortunately I need a non-VBA way of doing this....if that’s possible??
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top