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

tweacle

Rules violation
Joined
Nov 8, 2005
Messages
382
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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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),"")
 
Upvote 0
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)),"")
 
Upvote 0
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),"")
 
Upvote 0
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??
 
Upvote 0
I added a filter my test data set and the formula I gave you seems to work still.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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?

Disable AdBlock

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
Back
Top