Applying Sum Function to 2nd Dimension of Array

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

How do you sum a particulate dimension in an array? That is I have
2-D Array. I want to sum the 2nd dimension. Normally if it were 1-D
would go:

ArraySum = Application.WorksheetFunction.Sum(Array)

But if Array is a 2-D array, how do I qualify that I only want the 2n
dimension summed
 
I assume you mean a specifc column in the array.

ArraySum = Application.Sum(Application.Index(Array,0,3))

the above sums column 3. Using 0 as the second argument says use all rows.


Here is some sample code:

Sub SumArray()
Dim rng As Range
Dim v As Variant, v1 As Variant
Set rng = Range("A1").CurrentRegion
v = rng.Value
v1 = Application.Index(v, 0, 3)
For i = LBound(v1) To UBound(v1)
Debug.Print v1(i, LBound(v1, 2))
Next
Arraysum = Application.Sum(Application.Index(v, 0, 3))
Debug.Print Arraysum
End Sub

I believe this will limit you to an array of no larger than 5461 elements.
If your array is bigger, I guess you will have to loop through you array and
sum up the column yourself.
 
Tom said:
I assume you mean a specifc column in the array.

ArraySum = Application.Sum(Application.Index(Array,0,3))

the above sums column 3. Using 0 as the second argument says use all rows.
. . .
I believe this will limit you to an array of no larger than 5461 elements.
If your array is bigger, I guess you will have to loop through you array and
sum up the column yourself.
Click to expand...

Or, if the functions in the freely downloadable file at
http://home.pacbell.net are available to your workbook:

ArraySum = Application.Sum(ColumnVector(Array, 3))

The looping is pre-written into the ColumnVector function.

Alan Beban
 
Back
Top