Counting multiple lines of one order

[nocturne]

A1	B1	C1	D1
Order No:	BOX or WRAP	Medium	Large
MK23	1	1	0
MK23	1	0	1
MK23	1	1	0
MK24	1	1	0
MK24	1	0	1

<tbody>
</tbody>

B1, have sorted to 1 for BOX and 0 for Wrap

I would like to count, how many medium and how many large boxes for each Order No:

The output should be as below:

Order No:	Medium Box	Large Box
MK23	2	1
MK24	1	1

<tbody>
</tbody>

Thanks in advance

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order No:	BOX or WRAP	Medium	Large						Order No:	Medium Box	Large Box
2	MK23	1	1	0						MK23	2	1
3	MK23	1	0	1						MK24	1	1
4	MK23	1	1	0
5	MK24	1	1	0
6	MK24	1	0	1
User2

Cell Formulas
Range	Formula
K2	=COUNTIFS($A:$A,$J2,$B:$B,1,C:C,1)
L2	=COUNTIFS($A:$A,$J2,$B:$B,1,D:D,1)

 

[oldbrewer]

Order No:	BOX or WRAP	Medium	Large
MK23	1	1	0
MK23	1	0	1
MK23	1	1	0
MK24	1	1	0
MK24	1	0	1
B1, have sorted to 1 for BOX and 0 for Wrap
I would like to count, how many medium and how many large boxes for each Order No:
The output should be as below:
Order No:	Medium Box	Large Box
MK23	2	1
MK24	1	1
						Order No:	BOX or WRAP	Medium	Large
						MK23	1	1	0
						MK23	1	0	1
						MK23	1	1	0
						MK24	1	1	0
						MK24	1	0	1
						Medium	Large
					MK23	2	1
					MK24	1	1
				the 2 is obtained by
				=SUMPRODUCT(($G$22:$G$26=$F32)*($I$22:$I$26))

<colgroup><col span="12"></colgroup><tbody>
</tbody>

 

[nocturne]

Thanks both , but i do not know all the Order no:, there are about 200 orders. Preferably, i do not want to list them down one by one.

Do i get the list of the orders since they are multiple entries for each order?

Regards

 

[Gerald Higgins]

Have you considered a Pivot Table for this ?
That could do it I think.

 

[nocturne]

Not exactly blessed with pivot table ability. Thanks!

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1	Order No:	BOX or WRAP	Medium	Large						Order No:	Medium Box	Large Box
2	MK23	1	1	0						MK23	2	1
3	MK23	1	0	1						MK24	1	1
4	MK23	1	1	0						MK25	1	1
5	MK24	1	1	0
6	MK24	1	0	1
7	MK25	1	1	0
8	MK25	1	0	1
User2

Cell Formulas
Range	Formula
J2	=IFERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF(J$1:J1,$A$2:$A$8),0),0)),"")
K2	=COUNTIFS($A:$A,$J2,$B:$B,1,C:C,1)
L2	=COUNTIFS($A:$A,$J2,$B:$B,1,D:D,1)

 

[Gerald Higgins]

Not exactly blessed with pivot table ability.

In that case, maybe this is a good learning opportunity !
Why not try it ?