excel - How to sum a variable number of rows in a column? -

i have column of integers sorted in ascending sequence top bottom. each integer appears multiple times in column no integer appears same number of times.

moving down column, when integer changes, want insert row shows sum of identical integers above new row.

as move down column, i'm using counter count identical integers (and testing shows counter working), when insert counter r1c1 format (third line bottom below), returns zero. therefore, summation results summing current row 1 above (a circular reference), rather top row containing integer 1 above it.

my code:

private sub commandbutton1_click() dim counter integer counter = -1 range("f5").activate = 1 50 x = activecell(1, 1).value y = activecell(2, 1).value   if x = y   activecell.offset(1, 0).select   counter = counter - 1   else   call subtotal   counter = -1   end if next end sub  sub subtotal() activecell.offset(1, 0).select activecell.entirerow.insert activecell.formular1c1 = "=sum(r[" & counter & "]c:r[-1]c)" activecell.offset(1, 0).select end sub 

here solution (sort of conditional "running sum") can use/modify pertinent case. assuming simplicity 10 values in excel worksheet column a, "running count" appear in column b , "running sum" in column c:

1   3   3 1   2   2 4   3   12 1   1   1 2   2   4 4   2   8 5   2   10 4   1   4 2   1   2 5   1   5 

following simple vba code snippet job:

private sub commandbutton1_click() dim counter integer dim lastrow integer lastrow = cells(rows.count, "a").end(xlup).row = 1 lastrow     counter = 0     x = cells(i, 1).value     j = lastrow         if x = cells(j, 1).value             counter = counter + 1             cells(i, 2).value = counter             cells(i, 3).value = counter * x         end if     next j next end sub 

also, universal solution number of entries shown below:

private sub commandbutton1_click() dim counter integer dim lastrow integer lastrow = cells(rows.count, "a").end(xlup).row = 1 lastrow     counter = 0     x = cells(i, 1).value     j = lastrow         if x = cells(j, 1).value             counter = counter + 1             cells(i, 2).value = counter             cells(i, 3).value = counter * x         end if     next j next end sub 

hope may help. best regards,