MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Relatively Simple Array question providing me big headache

Posted by J.D. Cronin on December 28, 2001 1:08 PM

I have an array in the following manner
DateArray(i,30) and the elements are either 1 or 0.
I simply want to add up all of the for example if i = 10 then there might be 7 '1's' and 3 'zeros' I want a variable to be a sum of 7.

Any help would be appreciated

Posted by Jacob on December 28, 2001 2:56 PM


Something like this maybe:

If element = 1 then
Counter = Counter + 1
end if

So if there is a 1 counter will increase by one and if there is a zero it wont.


Posted by Bariloche on December 28, 2001 5:43 PM


Not sure why you're using a 2D array for this.

If I understand correctly you want to have an array of up to 30 items and then you want to find the sum of n of the items.

Option Base 1 'Sets the array index to start at 1 instead of 0

Sub ArraySum()

Dim MyArry(30) as integer

'Read values into the array
For i = 1 to 30
MyArray(i) = Cells (i,1).value
Next i

n = InputBox ("How many items to sum?")
MySum = 0

'Sum n of the items in the array
For i = 1 to n
MySum = MySum + MyArray(i)
Next i

Cells (1,2).Value = MySum

End Sub

The above should illustrate the techniques for summing an array. If you have more questions, post back.