CODEWITHSUNDEEP
arraysexcelmultidimensional-arrayvba
Matthew
Matthew

Reputation: 15

Excel VBA - Sum rows/columns in a 2D array

Let's say that we have a 4x5 array. How can I sum get a separate 1D array with 5 elements where every element represents the column-sum of elements from the first array?

Upvotes: 1

Views: 11042

Answers (3)

tom
tom

Reputation: 21

The code sums the column of YourArray; for sum of the row switch 0 and 1 

 With Application.WorksheetFunction
       sum_array = .sum(.Index(YourArray(), 0, 1)) 'switch 0 and 1 for row
    End With

Upvotes: 2

Matthew
Matthew

Reputation: 15

Dim Arr_1(1 to 4, 1 to 5) as Double
Dim Arr_2(1 to 5) as Double
For col = 1 to 5
    Arr_2(col) = 0
    for row = 1 to 4
        Arr_2(col) = Arr_2(col) + Arr_1(row, col)
    Next row
Next col

Thank you, user1228123!

Upvotes: 0

user1228123
user1228123

Reputation: 424

If done in the spreadsheet

I think I understand your question. At the bottom of each column simply put a SUM equation and sum over only the numbers in that column. For example if your array is in A1:E4 (first five columns and first four rows) put the following in cell A5:

=SUM(A1:A4)

And then copy cell A5 into cells B5 to E5.

EDIT - If done in VBA

Dim Arr_1(1 to 4, 1 to 5) as Double
Dim Arr_2(1 to 5) as Double
For col = 1 to 5
    Arr_2(col) = 0
    for row = 1 to 4
        Arr_2(col) = Arr_2(col) + Arr_1(row, col)
    Next row
Next col

Upvotes: 0

Related Questions