Counting multiple lines of one order

nocturne

New Member
Joined
Feb 27, 2019
Messages
6
A1B1C1D1
Order No:BOX or WRAPMediumLarge
MK23110
MK23101
MK23110
MK24110
MK24101

<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 BoxLarge Box
MK2321
MK2411

<tbody>
</tbody>

Thanks in advance
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
How about


Excel 2013/2016
ABCDEFGHIJKL
1Order No:BOX or WRAPMediumLargeOrder No:Medium BoxLarge Box
2MK23110MK2321
3MK23101MK2411
4MK23110
5MK24110
6MK24101
User2
Cell Formulas
RangeFormula
K2=COUNTIFS($A:$A,$J2,$B:$B,1,C:C,1)
L2=COUNTIFS($A:$A,$J2,$B:$B,1,D:D,1)
 

oldbrewer

Well-known Member
Joined
Apr 11, 2010
Messages
11,005
Order No:BOX or WRAPMediumLarge
MK23110
MK23101
MK23110
MK24110
MK24101
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 BoxLarge Box
MK2321
MK2411
Order No:
BOX or WRAPMediumLarge
MK23110
MK23101
MK23110
MK24110
MK24101
MediumLarge
MK2321
MK2411
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
Joined
Feb 27, 2019
Messages
6
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
Joined
Mar 26, 2007
Messages
9,171

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,043
Office Version
  1. 365
Platform
  1. Windows
Maybe


Excel 2013/2016
ABCDEFGHIJKL
1Order No:BOX or WRAPMediumLargeOrder No:Medium BoxLarge Box
2MK23110MK2321
3MK23101MK2411
4MK23110MK2511
5MK24110
6MK24101
7MK25110
8MK25101
User2
Cell Formulas
RangeFormula
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,598
Messages
5,832,644
Members
430,150
Latest member
amitk1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top