Calculating Number of Boxes

[sduttexcel]

Need to calculate number of boxes. Need to add "TM" in another row and highlight in yellow and another blank row. Total the qty.

5 In Big Box
3 or 4 in Medium Box
1 or 2 in small box

TM names vary from 1 to 15

Here is the example

Any formula to do this

[TABLE="width: 648"]
<tbody>[TR]
[TD]TM[/TD]
[TD]Address[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Zip[/TD]
[TD]Rep[/TD]
[TD]Qty[/TD]
[TD]Large Box[/TD]
[TD]Medium Box[/TD]
[TD]Small Box[/TD]
[/TR]
[TR]
[TD]Sunil Dutt[/TD]
[TD]123 Any street[/TD]
[TD]Anytown[/TD]
[TD]US[/TD]
[TD="align: right"]12345[/TD]
[TD]John Smith[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunil Dutt[/TD]
[TD]123 Any street[/TD]
[TD]Anytown[/TD]
[TD]US[/TD]
[TD="align: right"]12345[/TD]
[TD]Sam Lee[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunil Dutt[/TD]
[TD]123 Any street[/TD]
[TD]Anytown[/TD]
[TD]US[/TD]
[TD="align: right"]12345[/TD]
[TD]Bill Smith[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunil Dutt[/TD]
[TD]123 Any street[/TD]
[TD]Anytown[/TD]
[TD]US[/TD]
[TD="align: right"]12345[/TD]
[TD]Mike Roy[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sunil Dutt[/TD]
[TD]123 Any street[/TD]
[TD]Anytown[/TD]
[TD]US[/TD]
[TD="align: right"]12345[/TD]
[TD][/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD]Edward[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD]Ruby[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD]Michael[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD]zinc[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD]Albert[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George Low[/TD]
[TD]678 Super Rd[/TD]
[TD]Any City[/TD]
[TD]CA[/TD]
[TD="align: right"]94010[/TD]
[TD][/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshl9898]

Try:

H2 =IF(G2/5>=1,ROUNDDOWN(G2/5,0),0)

I2 =IF((G2-IFERROR(H2*5,0))/3>=1,ROUNDDOWN((G2-IFERROR(H2*5,0))/3,0),0)

J2 =ROUNDUP((G2-IF(IFERROR((I2*4),0)+IFERROR((H2*5),0)>G2,IFERROR((I2*3),0)+IFERROR((H2*5),0),IFERROR((I2*4),0)+IFERROR((H2*5),0)))/2,0)