Help summing values until next blank row or value change

[JumboCactuar]

Hi,
a bit stumpted on this one and not sure how to go about this:

see image below, the values in red are what i want to calculate:

I need to calculate total quantity for each order based on column D.
In image above, values B2:B4 belong to customer JTS so are summed.
sum until next blank cell in column D or different value.

Appreciate any help

 

[r1998]

Sorry wrong code

 

[JumboCactuar]

Bump

 

[jtakw]

Hi,

If you're willing to use a Helper Column (that you can hide, if you wish), you can do this:

Book1
	A	B	C	D	E
1		QTY	TOTAL	CUSTOMER	Helper
2	A	1	16	aa	aa
3	B	12			aa
4	C	3			aa
5					aa
6	D	2	2	bb	bb
7					bb
8	E	3	3	cc	cc
9	F	2	2	dd	dd
10	G	17	24	ee	ee
11	H	3			ee
12	I	2			ee
13	J	2			ee
14					ee
15	K	5	7	ff	ff
16	L	1			ff
17	M	1			ff
Sheet282

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(E$2:E2,E2)=1,SUMIF(E$2:E$17,E2,B$2:B$17),"")
E2	=IF(D2<>"",D2,E1)

Formulas copied down.

 

[JumboCactuar]

Perfect thank you

 

[jtakw]

You're welcome.

 

[JumboCactuar]

Hi,

If you're willing to use a Helper Column (that you can hide, if you wish), you can do this:

Book1
	A	B	C	D	E
1		QTY	TOTAL	CUSTOMER	Helper
2	A	1	16	aa	aa
3	B	12			aa
4	C	3			aa
5					aa
6	D	2	2	bb	bb
7					bb
8	E	3	3	cc	cc
9	F	2	2	dd	dd
10	G	17	24	ee	ee
11	H	3			ee
12	I	2			ee
13	J	2			ee
14					ee
15	K	5	7	ff	ff
16	L	1			ff
17	M	1			ff
Sheet282

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(E$2:E2,E2)=1,SUMIF(E$2:E$17,E2,B$2:B$17),"")
E2	=IF(D2<>"",D2,E1)

Formulas copied down.

Hi,
It's been a while but I found a problem with this formula

From your example, if Customer aa is listed again further down the column (example D18)

It's values are adding to the total in C2 which is wrong

 

[jtakw]

You didn't mention that there would be "Repeated" customers in Column D further down, amend Column E formula as follows, Column C formula remains the same, change/adjust cell references/range as needed:

Book1
	A	B	C	D	E
1		QTY	TOTAL	CUSTOMER	Helper
2	A	1	16	aa	aa1
3	B	12			aa1
4	C	3			aa1
5					aa1
6	D	2	2	bb	bb1
7					bb1
8	E	3	3	cc	cc1
9	F	2	2	dd	dd1
10	G	17	24	ee	ee1
11	H	3			ee1
12	I	2			ee1
13	J	2			ee1
14					ee1
15	K	5	7	ff	ff1
16	L	1			ff1
17	M	1			ff1
18					ff1
19		10	60	aa	aa2
20		20			aa2
21		30			aa2
22					aa2
23		15	75	bb	bb2
24		25			bb2
25		35			bb2
26					bb2
27		8	24	aa	aa3
28		16			aa3
Sheet282

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(E$2:E2,E2)=1,SUMIF(E$2:E$28,E2,B$2:B$28),"")
E2	=IF(D2<>"",D2&COUNTIF(D$2:D2,D2),E1)

 

[JumboCactuar]

Thank you @jtakw

 

[jtakw]

You're welcome, thanks for the feedback.