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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,630
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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),"")
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
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)),"")
 

tweacle

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

tweacle

Rules violation
Joined
Nov 8, 2005
Messages
382

ADVERTISEMENT

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??
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
2,061
Office Version
  1. 365
Platform
  1. Windows
I added a filter my test data set and the formula I gave you seems to work still.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,516
Members
430,437
Latest member
Emilycr

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
Top