How to sum every nth cell

[Bob L]

I'm looking for a way to sum every nth cell in a tab. In the example below, you can see that the consolidated view is a simple manual sum, but I'm looking for a way to automate the process. So if I'm adding a 4th store with quantity in cells C14 to E15, I'd like the sums in cells C2 to E3 to include them.

In this example, the quantities are every 3rd row, but I need to be able to modify this in the formula as it can change.

Thanks.

Book1
	A	B	C	D	E
1			2019	2020	2021
2	All	Shoes	8	30	23
3		Cakes	18	11	14
4
5	Store 1	Shoes	1	8	12
6		Cakes	3	7	5
7
8	Store 2	Shoes	5	14	1
9		Cakes	8	3	9
10
11	Store 3	Shoes	2	8	10
12		Cakes	7	1	0
13
14
15
Sheet1

Cell Formulas
Range	Formula
C2:E3	C2	=C5+C8+C11

 

[Phuoc]

Try this in C2:

=SUMIF($B$5:$B$13,$B2,C$5:C$13)

 

[mabbutt]

You can use this formula and amend it as you add more data:

=SUMIF(B2:B12,B2,C5:C12)

However, you might want to look at making the range dynamic.

 

[Habtest]

Hi, have you considered SUMIF, considering column B already has all you need? If not, SUMPRODCUT will do.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			2019	2020	2021		2019	2020	2021		2019	2020	2021
2	All	Shoes	8	30	23		8	30	23		8	30	23
3		Cakes	18	11	14		18	11	14		18	11	14
4
5	Store 1	Shoes	1	8	12		1	8	12		1	8	12
6		Cakes	3	7	5		3	7	5		3	7	5
7
8	Store 2	Shoes	5	14	1		5	14	1		5	14	1
9		Cakes	8	3	9		8	3	9		8	3	9
10
11	Store 3	Shoes	2	8	10		2	8	10		2	8	10
12		Cakes	7	1	0		7	1	0		7	1	0
Sheet1

Cell Formulas
Range	Formula
C2:E3	C2	=C5+C8+C11
G2:I3	G2	=SUMIF($B$5:$B$99,$B2,G$5:G$99)
K2:M3	K2	=SUMPRODUCT(--(MOD(ROW(K$5:K$99),3)=MOD(ROW(),3)),K$5:K$99)

 

[AhoyNC]

With Excel 365 you could use the FILTER function:

Book1
	A	B	C	D	E
1			2019	2020	2021
2	All	Shoes	8	30	23
3		Cakes	18	11	14
4
5	Store 1	Shoes	1	8	12
6		Cakes	3	7	5
7
8	Store 2	Shoes	5	14	1
9		Cakes	8	3	9
10
11	Store 3	Shoes	2	8	10
12		Cakes	7	1	0
Sheet1

Cell Formulas
Range	Formula
C2:E3	C2	=SUM(FILTER(C$5:C$500,$B$5:$B$500=$B2,0))