Excel for counting, sum, sort by models and maxim limit

[Gabrielle_erre]

Hi, I'm facing a challenge and I am sure that there is a way to deal with some help because there are smart people around...

I have different products per day, and I must organise to store them like that: each box can store max 60 products, every model in one box (if there is a different model with <60 I must use the quantity in one box, I can not mix products in boxes). I can load maximum 24 boxes on one pallet and after that using another pallet in between, and loading maximum 20 boxes. The ´final pallet´ will have 44 boxes high, and 2 pallets. If you can help me, I would like to find the formula to calculate the number of`final pallets` daily. I can try with single models per pallet (not only in single box), but more I would like to do it combined boxes per final pallet.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Model[/TD]
[TD]Products per day 1[/TD]
[TD]Product per day 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD]3000[/TD]
[TD]850[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]1100[/TD]
[TD]200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD]7020[/TD]
[TD]3500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Land Rover[/TD]
[TD]5203[/TD]
[TD]362[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]630[/TD]
[TD]7200[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Zetta[/TD]
[TD]875[/TD]
[TD]9300[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks! Gabrielle

 

[rmb4466]

Gabrielle,

Does this provide the data you're looking for?

[TABLE="width: 687"]
<tbody>[TR]
[TD]Models per box:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Boxes per final pallet:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]Day 1[/TD]
[TD="colspan: 3"]Day 2[/TD]
[/TR]
[TR]
[TD]Model[/TD]
[TD]Products[/TD]
[TD]Full boxes[/TD]
[TD]Models in short-fill box[/TD]
[TD]Products[/TD]
[TD]Full boxes[/TD]
[TD]Models in short-fill box[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD="align: right"]3000[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]850[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD="align: right"]7020[/TD]
[TD="align: right"]117[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3500[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Land Rover[/TD]
[TD="align: right"]5203[/TD]
[TD="align: right"]86[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]362[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD="align: right"]630[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]7200[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Zetta[/TD]
[TD="align: right"]875[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]9300[/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total boxes:[/TD]
[TD][/TD]
[TD="align: right"]299[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]360[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Final pallets:[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Remaining boxes:[/TD]
[TD][/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For the "Full boxes" column: =TRUNC(B8/$A$2)
For the "Models in short-fill box" column: =((B8/$A$2)-TRUNC(B8/$A$2))*$A$2
For "Total boxes": =SUM(C8:C13)+COUNTIF(D8:D13,">"&0)
For "Final pallets": =TRUNC(C15/$A$4)
For "Remaining boxes": =((C15/$A$4)-TRUNC(C15/$A$4))*A4

--Ryan

 

[Eric W]

Another take on it:

	A	B	C	D	E	F	G
Total Boxes
"Final" Pallets

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Products per day 1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]#boxes needed[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Product per day 2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Audi[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]3000[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]850[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]BMW[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1100[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Ford[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7020[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]117[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3500[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]59[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Land Rover[/TD]
[TD="align: right"]5203[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]87[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]362[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Toyota[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]630[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7200[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]120[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Zetta[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]875[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9300[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]155[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]299[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]360[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulase>[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=ROUNDUP(B2/60,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C9[/TH]
[TD="align: left"]=SUM(C2:C7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C10[/TH]
[TD="align: left"]=ROUNDUP(C9/44,0)[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

These numbers vary from rmb4466 because these include a partially filled pallet on each day.

Incidentally, if you don't need the intermediate steps, you can do the whole thing in one formula:

=ROUNDUP(SUMPRODUCT(ROUNDUP(B2:B7/60,0))/44,0)