Sum data based on coordinates

[chris3131]

	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

 

[Special-K99]

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.

 

[chris3131]

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]

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.

 

[chris3131]

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]

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
	A	B	C	D	E	F	G	H	I
1		a	b	c	d	Some		Examples	Result
2	1	5	2	7	100	16		b3	6
3	2	10	4	14	200	45		b3,c2,d4	420
4	3	15	6	21	300	721		b3,c2, d1:d3	620
5	4	2	8	28	400	48		d1:d3	600
6	5	3	7	9	11	85		a2:d2	228
7								some5	85
8								some2,some3	766
9								some3:some5	854
10								a1:some2	403
Hoja4

Put the following code in a module

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.

 

[Marcelo Branco]

Dante

Nice!


M

 

[DanteAmor]

Dante

Nice!


M

Thanks, I appreciate your comment

 

[chris3131]

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

 

[DanteAmor]

Find exercises with Solve