Summing a large number of cells without manually using autosum.

Milan05

New Member
Joined
Apr 15, 2021
Messages
5
Hi all,

I have a dataset with thousands of rows and therefore need a way to automate a lot of my calculations to speed up literally hours upon hours worth of manual effort.

Unfortunately I am not proficient enough in excel to automate it myself so after some help please.

I have a bunch of numbers on each row, basically signified by 'Dummy' within the 'Type' column.

At the bottom of each of my scores you will see a 'Summary' 'Type'. On this row is where I perform summary calculations.

#1 Instead of having to identify each 'Summary' field in thousands of rows and then autosum J16, J22, etc, I need a way for the formula to sum all rows above a particular 'Summary', relevant for that code and for that date, up until the next 'Summary' (not including the first lot of data which doesn't have a 'Summary' above it. Sounds confusing, let me give an example.

J16 needs to be the sum of J2 to J15. J23 needs to be the sum of J17 to J22. I imagine the best way to calculate this would be to read column 'C' which will always have either the word 'Dummy', or the word 'Summary', and therefore when you get to a summary row (J16), you read all the 'Dummy' rows above (column c), and therefore sum the values for J16. As I mentioned, you will need to find a way to have this work even for the first lot of data, as the first lot of data doesn't have a 'Summary' row above it, it just starts at 'Dummy'.



This way of summing data is relevant for J16, K16, L16, M16, N16, O16, P16

What I want to do is filter 'Type' by 'Summary' within my workbook, use the formula I am hoping someone provides me, and then drag that down all my 'Summary' rows.

Refer to BLUE highlighted fields.


#2. The issue I have with column K is that my Summed total as per #1 is not always a set number of rows. I need a way to automatically divide the averages by the total summed row (eg K2 to K15, divide by K16). As mentioned, I dont know how to do this as the number of rows is never consistent.

What I want to do is filter 'Type' by 'Dummy' within my workbook, use the formula I am hoping someone provides me, and then drag that down all of my 'Dummy rows'

Refer to GREEN highlighted fields.



Help would greatly be appreciated, to manually scroll through thousands of lines and autosum each of these blue highlighted cells would take me forever. Thanks!


















1618714386995.png









Book2
ABCDEFGHIJKLMNOP
1UIDDateTypeTickerDummy dataNumber #1Number # 2Number # 3Number #1 + Number #3Average Ratio of average to total average Weighted score #1Score #2Ratio of score #2 to total score #2Score #3Score adjusted
2120/04/2015DummyABCDDummy414.130731130914.24%44.0156.416.53%12.392.047354451
3220/04/2015DummyABCDDummy123.728830029413.55%39.8444.413.01%4.560.593027095
4320/04/2015DummyABCDDummy133141154147.56.80%10.033911.43%28.973.310574099
5420/04/2015DummyABCDDummy271.3169196182.58.41%15.3535.110.28%19.752.030921209
6520/04/2015DummyABCDDummy221.521223422310.28%22.92339.67%10.080.974445484
7620/04/2015DummyABCDDummy191.2265284274.512.65%34.7322.86.68%-11.93-0.797072781
8720/04/2015DummyABCDDummy64.15763602.77%1.6624.67.21%22.941.653499954
9820/04/2015DummyABCDDummy72.1115122118.55.46%6.4714.74.31%8.230.354360262
10920/04/2015DummyABCDDummy62.32329261.20%0.3113.84.04%13.490.545385359
111020/04/2015DummyABCDDummy33.1031.50.07%0.009.32.72%9.300.253385159
121120/04/2015DummyABCDDummy61.81821881858.53%15.7810.83.16%-4.98-0.157444074
131220/04/2015DummyABCDDummy81.728729529113.41%39.0313.63.98%-25.43-1.013424978
141320/04/2015DummyABCDDummy81.62331271.24%0.3412.83.75%12.460.467444819
151420/04/2015DummyABCDDummy101.12535301.38%0.41113.22%10.590.341156568
161520/04/2015SummaryABCDDummy2169.5100.00%230.89341.3100.00%110.4110.60361263
171622/06/2015DummyEFGHDummy422.1275117960.0089.0489.040
181722/06/2015DummyEFGHDummy242.311261501380.0055.4455.440
191822/06/2015DummyEFGHDummy32.86757876.50.008.588.580
201922/06/2015DummyEFGHDummy60.917278750.005.465.460
212022/06/2015DummyEFGHDummy32.57848785.50.007.717.710
222122/06/2015DummyEFGHDummy31.23120123121.50.003.693.690
232222/06/2015SummaryEFGHDummy592.50.00169.920
242322/06/2015DummyIJKLDummy5.312.2265.15270.45267.80.0064.6664.660
252422/06/2015DummyIJKLDummy4.19.9281.9286283.950.0040.5940.590
262522/06/2015SummaryIJKLDummy551.750.00105.250
MrExcel
Cell Formulas
RangeFormula
O2:O15,O17:O22,O24:O25O2=M2-L2
P2:P15,P17:P22,P24:P25P2=O2*N2
J16:P16O16=SUM(O2:O15)
O23:P23,L23,J23O23=SUM(O17:O22)
O26:P26,L26,J26O26=SUM(O24:O25)
I2:I15,I24:I25,I17:I22I2=F2+H2
K2:K15,N2:N15K2=J2/J$16
M2:M15,M17:M22,M24:M25M2=F2*G2
J2:J15,J17:J22,J24:J25J2=AVERAGE(H2:I2)
L2:L15,L17:L22,L24:L25L2=J2*K2
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
150
Office Version
  1. 365
Platform
  1. Windows
Please try

Book1
ABCDEFGHIJKLMNOP
1UIDDateTypeTickerDummy dataNumber #1Number # 2Number # 3Number #1 + Number #3Average Ratio of average to total average Weighted score #1Score #2Ratio of score #2 to total score #2Score #3Score adjusted
2142114DummyABCDDummy414.130731130914.24%44.010601556.416.53%12.389398482.047354451
3242114DummyABCDDummy123.728830029413.55%39.841438144.413.01%4.5585618810.593027095
4342114DummyABCDDummy133141154147.56.80%10.02823233911.43%28.971767693.310574099
5442114DummyABCDDummy271.3169196182.58.41%15.352039635.110.28%19.747960362.030921209
6542114DummyABCDDummy221.521223422310.28%22.9218714339.67%10.07812860.974445484
7642114DummyABCDDummy191.2265284274.512.65%34.731620222.86.68%-11.9316202-0.79707278
8742114DummyABCDDummy64.15763602.77%1.6593685224.67.21%22.940631481.653499954
9842114DummyABCDDummy72.1115122118.55.46%6.4725743314.74.31%8.2274256740.354360262
10942114DummyABCDDummy62.32329261.20%0.3115925313.84.04%13.488407470.545385359
111042114DummyABCDDummy33.1031.50.07%0.001037119.32.72%9.2989628950.253385159
121142114DummyABCDDummy61.81821881858.53%15.775524310.83.16%-4.97552431-0.15744407
131242114DummyABCDDummy81.728729529113.41%39.03249613.63.98%-25.432496-1.01342498
141342114DummyABCDDummy81.62331271.24%0.3360221212.83.75%12.463977880.467444819
151442114DummyABCDDummy101.12535301.38%0.41484213113.22%10.585157870.341156568
161542114SummaryABCDDummy 2169.5100.00%230.88926341.3100.00%110.410739810.60361263
171642177DummyEFGHDummy422.12751179616.20%15.554430489.0452.40%73.4855696238.50726883
181742177DummyEFGHDummy242.3112615013823.29%32.141772255.4432.63%23.298227857.601540442
191842177DummyEFGHDummy32.86757876.512.91%9.877215198.585.05%-1.29721519-0.06550204
201942177DummyEFGHDummy60.9172787512.66%9.493670895.463.21%-4.03367089-0.12961301
212042177DummyEFGHDummy32.57848785.514.43%12.33797477.714.54%-4.62797468-0.20999108
222142177DummyEFGHDummy31.23120123121.520.51%24.91518993.692.17%-21.2251899-0.46092838
232242177SummaryEFGHDummy 592.5100.00%104.320253169.92100.00%65.5997468445.24277475
242342177DummyIJKLDummy5.312.2265.15270.45267.848.54%129.9806864.6661.43%-65.3206797-40.1295501
252442177DummyIJKLDummy4.19.9281.9286283.9551.46%146.1306840.5938.57%-105.54068-40.7021015
262542177SummaryIJKLDummy 551.75100.00%276.111359105.25100.00%-170.861359-80.8316516
Sheet1
Cell Formulas
RangeFormula
I2:I26I2=IF($C2="Summary","",F2+H2)
J2:J26J2=IF($C2="Summary",SUMIFS(J$1:J1,$D$1:$D1,$D2),AVERAGE(H2,H2+F2))
K2:K26,N2:N26K2=IF($C2="Summary",1,J2/VLOOKUP("Summary",$C3:J27,COLUMNS($C3:J27),0))
L2:L26,P2:P26L2=IF($C2="Summary",SUMIFS(L$1:L1,$D$1:$D1,$D2),J2*K2)
M2:M26M2=IF($C2="Summary",SUMIFS(M$1:M1,$D$1:$D1,$D2),F2*G2)
O2:O26O2=IF($C2="Summary",SUMIFS(O$1:O1,$D$1:$D1,$D2),M2-L2)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,528
Messages
5,636,847
Members
416,945
Latest member
Himu

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