Summing a large number of cells without manually using autosum.

[Milan05]

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!

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	UID	Date	Type	Ticker	Dummy data	Number #1	Number # 2	Number # 3	Number #1 + Number #3	Average	Ratio of average to total average	Weighted score #1	Score #2	Ratio of score #2 to total score #2	Score #3	Score adjusted
2	1	20/04/2015	Dummy	ABCD	Dummy	4	14.1	307	311	309	14.24%	44.01	56.4	16.53%	12.39	2.047354451
3	2	20/04/2015	Dummy	ABCD	Dummy	12	3.7	288	300	294	13.55%	39.84	44.4	13.01%	4.56	0.593027095
4	3	20/04/2015	Dummy	ABCD	Dummy	13	3	141	154	147.5	6.80%	10.03	39	11.43%	28.97	3.310574099
5	4	20/04/2015	Dummy	ABCD	Dummy	27	1.3	169	196	182.5	8.41%	15.35	35.1	10.28%	19.75	2.030921209
6	5	20/04/2015	Dummy	ABCD	Dummy	22	1.5	212	234	223	10.28%	22.92	33	9.67%	10.08	0.974445484
7	6	20/04/2015	Dummy	ABCD	Dummy	19	1.2	265	284	274.5	12.65%	34.73	22.8	6.68%	-11.93	-0.797072781
8	7	20/04/2015	Dummy	ABCD	Dummy	6	4.1	57	63	60	2.77%	1.66	24.6	7.21%	22.94	1.653499954
9	8	20/04/2015	Dummy	ABCD	Dummy	7	2.1	115	122	118.5	5.46%	6.47	14.7	4.31%	8.23	0.354360262
10	9	20/04/2015	Dummy	ABCD	Dummy	6	2.3	23	29	26	1.20%	0.31	13.8	4.04%	13.49	0.545385359
11	10	20/04/2015	Dummy	ABCD	Dummy	3	3.1	0	3	1.5	0.07%	0.00	9.3	2.72%	9.30	0.253385159
12	11	20/04/2015	Dummy	ABCD	Dummy	6	1.8	182	188	185	8.53%	15.78	10.8	3.16%	-4.98	-0.157444074
13	12	20/04/2015	Dummy	ABCD	Dummy	8	1.7	287	295	291	13.41%	39.03	13.6	3.98%	-25.43	-1.013424978
14	13	20/04/2015	Dummy	ABCD	Dummy	8	1.6	23	31	27	1.24%	0.34	12.8	3.75%	12.46	0.467444819
15	14	20/04/2015	Dummy	ABCD	Dummy	10	1.1	25	35	30	1.38%	0.41	11	3.22%	10.59	0.341156568
16	15	20/04/2015	Summary	ABCD	Dummy					2169.5	100.00%	230.89	341.3	100.00%	110.41	10.60361263
17	16	22/06/2015	Dummy	EFGH	Dummy	42	2.12	75	117	96		0.00	89.04		89.04	0
18	17	22/06/2015	Dummy	EFGH	Dummy	24	2.31	126	150	138		0.00	55.44		55.44	0
19	18	22/06/2015	Dummy	EFGH	Dummy	3	2.86	75	78	76.5		0.00	8.58		8.58	0
20	19	22/06/2015	Dummy	EFGH	Dummy	6	0.91	72	78	75		0.00	5.46		5.46	0
21	20	22/06/2015	Dummy	EFGH	Dummy	3	2.57	84	87	85.5		0.00	7.71		7.71	0
22	21	22/06/2015	Dummy	EFGH	Dummy	3	1.23	120	123	121.5		0.00	3.69		3.69	0
23	22	22/06/2015	Summary	EFGH	Dummy					592.5		0.00			169.92	0
24	23	22/06/2015	Dummy	IJKL	Dummy	5.3	12.2	265.15	270.45	267.8		0.00	64.66		64.66	0
25	24	22/06/2015	Dummy	IJKL	Dummy	4.1	9.9	281.9	286	283.95		0.00	40.59		40.59	0
26	25	22/06/2015	Summary	IJKL	Dummy					551.75		0.00			105.25	0
MrExcel

Cell Formulas
Range	Formula
O2:O15,O17:O22,O24:O25	O2	=M2-L2
P2:P15,P17:P22,P24:P25	P2	=O2*N2
J16:P16	O16	=SUM(O2:O15)
O23:P23,L23,J23	O23	=SUM(O17:O22)
O26:P26,L26,J26	O26	=SUM(O24:O25)
I2:I15,I24:I25,I17:I22	I2	=F2+H2
K2:K15,N2:N15	K2	=J2/J$16
M2:M15,M17:M22,M24:M25	M2	=F2*G2
J2:J15,J17:J22,J24:J25	J2	=AVERAGE(H2:I2)
L2:L15,L17:L22,L24:L25	L2	=J2*K2

 

[Milan05]

Any ideas on this one? Cheers

 

[Tom.Jones]

You get an answer here:
Cumulative Sums based on multiple fields

 

[Milan05]

You get an answer here:
Cumulative Sums based on multiple fields

Yeah that post of mine is a cumulative sum running down the worksheet. That does not apply here as I am not looking for a running total. My question is that thread is vastly different to what I am looking for here.

 

[Bo_Ry]

Please try

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	UID	Date	Type	Ticker	Dummy data	Number #1	Number # 2	Number # 3	Number #1 + Number #3	Average	Ratio of average to total average	Weighted score #1	Score #2	Ratio of score #2 to total score #2	Score #3	Score adjusted
2	1	42114	Dummy	ABCD	Dummy	4	14.1	307	311	309	14.24%	44.0106015	56.4	16.53%	12.38939848	2.047354451
3	2	42114	Dummy	ABCD	Dummy	12	3.7	288	300	294	13.55%	39.8414381	44.4	13.01%	4.558561881	0.593027095
4	3	42114	Dummy	ABCD	Dummy	13	3	141	154	147.5	6.80%	10.0282323	39	11.43%	28.97176769	3.310574099
5	4	42114	Dummy	ABCD	Dummy	27	1.3	169	196	182.5	8.41%	15.3520396	35.1	10.28%	19.74796036	2.030921209
6	5	42114	Dummy	ABCD	Dummy	22	1.5	212	234	223	10.28%	22.9218714	33	9.67%	10.0781286	0.974445484
7	6	42114	Dummy	ABCD	Dummy	19	1.2	265	284	274.5	12.65%	34.7316202	22.8	6.68%	-11.9316202	-0.79707278
8	7	42114	Dummy	ABCD	Dummy	6	4.1	57	63	60	2.77%	1.65936852	24.6	7.21%	22.94063148	1.653499954
9	8	42114	Dummy	ABCD	Dummy	7	2.1	115	122	118.5	5.46%	6.47257433	14.7	4.31%	8.227425674	0.354360262
10	9	42114	Dummy	ABCD	Dummy	6	2.3	23	29	26	1.20%	0.31159253	13.8	4.04%	13.48840747	0.545385359
11	10	42114	Dummy	ABCD	Dummy	3	3.1	0	3	1.5	0.07%	0.00103711	9.3	2.72%	9.298962895	0.253385159
12	11	42114	Dummy	ABCD	Dummy	6	1.8	182	188	185	8.53%	15.7755243	10.8	3.16%	-4.97552431	-0.15744407
13	12	42114	Dummy	ABCD	Dummy	8	1.7	287	295	291	13.41%	39.032496	13.6	3.98%	-25.432496	-1.01342498
14	13	42114	Dummy	ABCD	Dummy	8	1.6	23	31	27	1.24%	0.33602212	12.8	3.75%	12.46397788	0.467444819
15	14	42114	Dummy	ABCD	Dummy	10	1.1	25	35	30	1.38%	0.41484213	11	3.22%	10.58515787	0.341156568
16	15	42114	Summary	ABCD	Dummy					2169.5	100.00%	230.88926	341.3	100.00%	110.4107398	10.60361263
17	16	42177	Dummy	EFGH	Dummy	42	2.12	75	117	96	16.20%	15.5544304	89.04	52.40%	73.48556962	38.50726883
18	17	42177	Dummy	EFGH	Dummy	24	2.31	126	150	138	23.29%	32.1417722	55.44	32.63%	23.29822785	7.601540442
19	18	42177	Dummy	EFGH	Dummy	3	2.86	75	78	76.5	12.91%	9.87721519	8.58	5.05%	-1.29721519	-0.06550204
20	19	42177	Dummy	EFGH	Dummy	6	0.91	72	78	75	12.66%	9.49367089	5.46	3.21%	-4.03367089	-0.12961301
21	20	42177	Dummy	EFGH	Dummy	3	2.57	84	87	85.5	14.43%	12.3379747	7.71	4.54%	-4.62797468	-0.20999108
22	21	42177	Dummy	EFGH	Dummy	3	1.23	120	123	121.5	20.51%	24.9151899	3.69	2.17%	-21.2251899	-0.46092838
23	22	42177	Summary	EFGH	Dummy					592.5	100.00%	104.320253	169.92	100.00%	65.59974684	45.24277475
24	23	42177	Dummy	IJKL	Dummy	5.3	12.2	265.15	270.45	267.8	48.54%	129.98068	64.66	61.43%	-65.3206797	-40.1295501
25	24	42177	Dummy	IJKL	Dummy	4.1	9.9	281.9	286	283.95	51.46%	146.13068	40.59	38.57%	-105.54068	-40.7021015
26	25	42177	Summary	IJKL	Dummy					551.75	100.00%	276.111359	105.25	100.00%	-170.861359	-80.8316516
Sheet1

Cell Formulas
Range	Formula
I2:I26	I2	=IF($C2="Summary","",F2+H2)
J2:J26	J2	=IF($C2="Summary",SUMIFS(J$1:J1,$D$1:$D1,$D2),AVERAGE(H2,H2+F2))
K2:K26,N2:N26	K2	=IF($C2="Summary",1,J2/VLOOKUP("Summary",$C3:J27,COLUMNS($C3:J27),0))
L2:L26,P2:P26	L2	=IF($C2="Summary",SUMIFS(L$1:L1,$D$1:$D1,$D2),J2*K2)
M2:M26	M2	=IF($C2="Summary",SUMIFS(M$1:M1,$D$1:$D1,$D2),F2*G2)
O2:O26	O2	=IF($C2="Summary",SUMIFS(O$1:O1,$D$1:$D1,$D2),M2-L2)