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

TM	Address	City	State	Zip	Rep	Qty	Large Box	Medium Box	Small Box
Sunil Dutt	123 Any street	Anytown	US	12345	John Smith	4
Sunil Dutt	123 Any street	Anytown	US	12345	Sam Lee	2
Sunil Dutt	123 Any street	Anytown	US	12345	Bill Smith	3
Sunil Dutt	123 Any street	Anytown	US	12345	Mike Roy	7
Sunil Dutt	123 Any street	Anytown	US	12345		16	3		1
George Low	678 Super Rd	Any City	CA	94010	Edward	2
George Low	678 Super Rd	Any City	CA	94010	Ruby	1
George Low	678 Super Rd	Any City	CA	94010	Michael	5
George Low	678 Super Rd	Any City	CA	94010	zinc	4
George Low	678 Super Rd	Any City	CA	94010	Albert	3
George Low	678 Super Rd	Any City	CA	94010		15	2

<tbody>
</tbody>

 

[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)