# How do I do autosum at every change in numbers in specific column

#### tweacle

##### Rules violation
Hi

I have a spreadsheet and it has data in columns A-K and what im trying to do is at every change of number in column A, I need to do an autosum of the values of data in column I and input the total into column J .

Lets say I have the same number in A1-A9 and I have values in Column I, When the number changes in A10 I need the data in I1-I9 all added together and then a total put in J9.

I then need this to repeat down the sheet. The same number in column A varies depending on how many entries there are.

Any ideas?

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
If he numbers in A are sorted then this should work. copy formula down

Book1
ABCDEFGHIJ
111
212
313
414
515
616
717
818
91945
10710
11711
1271233
13613
1461427
Sheet2
Cell Formulas
RangeFormula
J1=IF(A1<>A2,SUMPRODUCT(--(\$A\$1:A1=A1),\$I\$1:I1),"")

Please try this formula. Put it in cell J2 and copy down. The principle is if Col A Changes and the next cell in Col A isn't blank the sum the values above.

=IF(AND(A2<>A3,A3<>""),SUM(OFFSET(J2,-(ROW(J2)-IFERROR(MATCH(1E+300,\$J\$1:J1),1)-1),-9,ROW(J2)-IFERROR(MATCH(1E+300,\$J\$1:J1),1),1)),"")

Really appriciate that.

Can I just ask is my formula correct if I want to do the same as originally stated but using column B as the one to look for changes in.

=IF(B1<>B2,SUMPRODUCT(--(\$B\$1:B1=A1),\$I\$1:I1),"")

Many thanks

Ive tried Scott's formula and the issue is now that I have found out I have to filter data and the formula then dont work. Is there anyway round this??

I added a filter my test data set and the formula I gave you seems to work still.

Apologies it was the way I was doing it. Yes it does work. Thanks

Replies
26
Views
355
Replies
4
Views
644
Replies
2
Views
209
Replies
2
Views
131
Replies
4
Views
79

1,214,714
Messages
6,121,047
Members
449,007
Latest member
cloamp

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back