SUM - N Rows above (Value of N comes from two columns one row above)

[sm789]

Dear All,

I am stuck and need a little help. Seems like a simple problem.

I have a list. Every day we get sales data from different locations. Each location gets one row. Then data goes in the subsequent columns. The number of rows is dynamic depending on how many locations reported for that day.

Here is the structure:

Column A has date, Column B has serial numbers of data points for that date, Column C has location, Column D through K has data for that location.
For each date, the row after the last location has totals. Here is the issue. Since the number of rows to total come from Column B (Column B is reset at 1 for each date), how do I create a SUM formula to add n number of rows when n is a value in one row above and two columns to the left?

Thank you
Sam

 

[Peter_SSs]

Would something like this work for you?

Put the formula in E7, copy across, then copy that row of formulas to the other TOTAL rows.

20 10 19.xlsm
	A	B	C	D	E	F	G
1
2	12/10/2020				4	1	6
3	12/10/2020				3	8	2
4	12/10/2020				6	9	2
5	12/10/2020				8	3	8
6	12/10/2020				8	7	2
7	12/10/2020		TOTAL		29	28	20
8	13/10/2020				7	7	3
9	13/10/2020				6	2	5
10	13/10/2020		TOTAL		13	9	8
11	14/10/2020				6	9	6
12	14/10/2020				1	6	1
13	14/10/2020				5	9	2
14	14/10/2020				2	2	7
15	14/10/2020				9	3	4
16	14/10/2020				2	9	5
17	14/10/2020				2	8	3
18	14/10/2020		TOTAL		27	46	28
19	15/10/2020				9	2	6
20	15/10/2020				1	2	5
21	15/10/2020				8	5	6
22	15/10/2020				8	8	8
23	15/10/2020				4	1	3
24	15/10/2020				6	5	3
25	15/10/2020				5	4	7
26	15/10/2020				3	2	6
27	15/10/2020				4	5	3
28	15/10/2020				2	9	2
29	15/10/2020				4	1	8
30	15/10/2020		TOTAL		54	44	57
Sum Variable rows

Cell Formulas
Range	Formula
E7:G7,E30:G30,E18:G18,E10:G10	E7	=SUMIF($A$2:$A6,$A7,E$2:E6)

 

[sm789]

Would something like this work for you?

Put the formula in E7, copy across, then copy that row of formulas to the other TOTAL rows.

20 10 19.xlsm
	A	B	C	D	E	F	G
1
2	12/10/2020				4	1	6
3	12/10/2020				3	8	2
4	12/10/2020				6	9	2
5	12/10/2020				8	3	8
6	12/10/2020				8	7	2
7	12/10/2020		TOTAL		29	28	20
8	13/10/2020				7	7	3
9	13/10/2020				6	2	5
10	13/10/2020		TOTAL		13	9	8
11	14/10/2020				6	9	6
12	14/10/2020				1	6	1
13	14/10/2020				5	9	2
14	14/10/2020				2	2	7
15	14/10/2020				9	3	4
16	14/10/2020				2	9	5
17	14/10/2020				2	8	3
18	14/10/2020		TOTAL		27	46	28
19	15/10/2020				9	2	6
20	15/10/2020				1	2	5
21	15/10/2020				8	5	6
22	15/10/2020				8	8	8
23	15/10/2020				4	1	3
24	15/10/2020				6	5	3
25	15/10/2020				5	4	7
26	15/10/2020				3	2	6
27	15/10/2020				4	5	3
28	15/10/2020				2	9	2
29	15/10/2020				4	1	8
30	15/10/2020		TOTAL		54	44	57
Sum Variable rows

Cell Formulas
Range	Formula
E7:G7,E30:G30,E18:G18,E10:G10	E7	=SUMIF($A$2:$A6,$A7,E$2:E6)

Thank you Sir! This requires us to modify the Total formula each time depending on the number of rows above. I was hoping for a formula that can be copied without having to modify it. Basically I was wondering if it is possible to do something like SUM(from cell above to n number of rows; where n is from Column B of the row above). Thanks

 

[sm789]

Sorry! I spoke too soon. This works for me. Simple and beautiful!

Thank you

 

[Peter_SSs]

You're welcome.

You do have to adjust the first formula only each day to account for the number of rows in that first section, that is all.
The other option is to have a small macro to insert the formulas for you.

Example.
Before the macro

20 10 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	12/10/2020	1			4	1	6	2	9	2	2
2	12/10/2020	2			3	8	2	8	3	3	9
3	12/10/2020	3			6	9	2	4	2	2	9
4	12/10/2020	4			8	3	8	8	3	2	7
5	12/10/2020	5			8	7	2	6	8	2	9
6	12/10/2020		TOTAL
7	13/10/2020	1			7	7	3	2	9	2	9
8	13/10/2020	2			6	2	5	2	2	8	1
9	13/10/2020		TOTAL
10	14/10/2020	1			6	9	6	7	6	9	4
11	14/10/2020	2			1	6	1	6	9	9	9
12	14/10/2020	3			5	9	2	6	9	7	1
13	14/10/2020	4			2	2	7	7	7	4	5
14	14/10/2020	5			9	3	4	2	5	8	1
15	14/10/2020	6			2	9	5	3	6	9	6
16	14/10/2020	7			2	8	3	3	4	6	7
17	14/10/2020		TOTAL
18	15/10/2020	1			9	2	6	3	4	1	1
19	15/10/2020	2			1	2	5	5	7	5	6
20	15/10/2020	3			8	5	6	2	6	7	2
21	15/10/2020	4			8	8	8	9	3	4	3
22	15/10/2020	5			4	1	3	1	1	9	5
23	15/10/2020	6			6	5	3	3	4	8	9
24	15/10/2020	7			5	4	7	6	1	8	2
25	15/10/2020	8			3	2	6	6	3	1	5
26	15/10/2020	9			4	5	3	4	3	4	2
27	15/10/2020	10			2	9	2	5	3	2	8
28	15/10/2020	11			4	1	8	3	8	4	4
29	15/10/2020		TOTAL
Sum Variable rows

Macro:

Sub InsertTotalFormulas()
  Intersect(Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).EntireRow, Columns("E:K")).FormulaR1C1 = "=SUMIF(R1C1:R[-1]C1,RC1,R1C:R[-1]C)"
End Sub

After the macro:

20 10 19.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	12/10/2020	1			4	1	6	2	9	2	2
2	12/10/2020	2			3	8	2	8	3	3	9
3	12/10/2020	3			6	9	2	4	2	2	9
4	12/10/2020	4			8	3	8	8	3	2	7
5	12/10/2020	5			8	7	2	6	8	2	9
6	12/10/2020		TOTAL		29	28	20	28	25	11	36
7	13/10/2020	1			7	7	3	2	9	2	9
8	13/10/2020	2			6	2	5	2	2	8	1
9	13/10/2020		TOTAL		13	9	8	4	11	10	10
10	14/10/2020	1			6	9	6	7	6	9	4
11	14/10/2020	2			1	6	1	6	9	9	9
12	14/10/2020	3			5	9	2	6	9	7	1
13	14/10/2020	4			2	2	7	7	7	4	5
14	14/10/2020	5			9	3	4	2	5	8	1
15	14/10/2020	6			2	9	5	3	6	9	6
16	14/10/2020	7			2	8	3	3	4	6	7
17	14/10/2020		TOTAL		27	46	28	34	46	52	33
18	15/10/2020	1			9	2	6	3	4	1	1
19	15/10/2020	2			1	2	5	5	7	5	6
20	15/10/2020	3			8	5	6	2	6	7	2
21	15/10/2020	4			8	8	8	9	3	4	3
22	15/10/2020	5			4	1	3	1	1	9	5
23	15/10/2020	6			6	5	3	3	4	8	9
24	15/10/2020	7			5	4	7	6	1	8	2
25	15/10/2020	8			3	2	6	6	3	1	5
26	15/10/2020	9			4	5	3	4	3	4	2
27	15/10/2020	10			2	9	2	5	3	2	8
28	15/10/2020	11			4	1	8	3	8	4	4
29	15/10/2020		TOTAL		54	44	57	47	43	53	47
Sum Variable rows

Cell Formulas
Range	Formula
E6:K6,E29:K29,E17:K17,E9:K9	E6	=SUMIF($A$1:$A5,$A6,E$1:E5)

 

[Peter_SSs]

BTW, I suggest that you ..

• update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
• investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[sm789]

Thank you very much! The formula works very well. I will try the vba macro as well simply because I love to play around with macros and learn new things.

Have updated my profile! Thank you again!