# Counting multiple lines of one order

#### nocturne

##### New Member
 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>

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### oldbrewer

##### Board Regular
 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

##### New Member
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

##### Well-known Member

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

#### nocturne

##### New Member
Not exactly blessed with pivot table ability. Thanks!

Maybe

#### Gerald Higgins

##### Well-known Member
Not exactly blessed with pivot table ability.

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

Replies
1
Views
191
Replies
6
Views
108
Replies
6
Views
100
Replies
15
Views
215
Replies
9
Views
100