Add up a range only if divisible by x

[CafeRoaster]

Hello there. I run a coffee roastery, and we offer several sizes. I'm attempting to build a sheet that tells our packaging team how many 2-lb bags and how many 5-lb bags to prepare. Customers can order by the pound. We have a 2-lb bag that all weights below or equal to 2-lbs go into, and a 5-lb bag that all weights below or equal to 5-lbs go into.

For instance, if someone order 1 lb, that goes into a 2-lb bag. If someone orders 3 lbs, that goes into a 5-lb bag.

These amounts get filled into Column B as however much they ordered. Column A contains the customer account name and the word BULK.

I want to be able to distinguish between what goes into a 2-lb bag and what goes into a 5-lb bag.

Currently, I have this, but it isn't doing what I need it to do.

=mod(SUMIFS(B11:B82,$A$11:$A$82,"*BULK*"),5)

In order for this to work, I cannot simply use "<=2" and ">2", because if someone orders 12 lbs, for instance, that's 2x 5-lb bags and one 2-lb bag.

Cell B5 is doing the math for finding <=2, and Cell B6 is doing math for >2.

 

[jdellasala]

Assuming you have columns - Weight, 5 lb Bags, 2 lb Bags.

5 lb Bags = INT(Weight/5)
2 lb Bags = ROUNDUP( (Weight - (5 lb Bags * 5))/2, 0 )

Here's what I came up with:

		=INT( [@Weight] /5)	=ROUNDUP( ([@Weight]-([@[5 Lb Bags]]*5))/2, 0 )
Order	Weight	5 Lb Bags	2 Lb Bags
1	14	2	2
2	1	0	1
3	5	1	0
4	13	2	2
5	11	2	1
6	4	0	2
7	7	1	1
8	8	1	2
9	1	0	1
10	3	0	2
11	6	1	1
12	1	0	1
13	13	2	2
14	2	0	1
15	5	1	0
16	10	2	0
17	9	1	2
18	17	3	1
19	19	3	2
20	6	1	1

 

[Eric W]

Here's another take on it:

Book1
	A	B	C	D
1	Order	Weight	5 Lb Bags	2 Lb Bags
2	1	14	3	0
3	2	1	0	1
4	3	5	1	0
5	4	13	3	0
6	5	11	2	1
7	6	4	1	0
8	7	7	1	1
9	8	8	2	0
10	9	1	0	1
11	10	3	1	0
12	11	6	1	1
13	12	1	0	1
14	13	13	3	0
15	14	2	0	1
16	15	5	1	0
17	16	10	2	0
18	17	9	2	0
19	18	17	3	1
20	19	19	4	0
21	20	6	1	1
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=INT(B2/5)+(MOD(B2,5)>2)
D2:D21	D2	=(B2>C2*5)+0

This will result in using fewer total bags, but more of the 5-pound bags. Compare orders 1 and 4 for example to see the difference.

 

[Phuoc]

Or try this in C2:

=ROUND(B2/5,0)

 

[Phuoc]

Book1
	A	B	C	D
1	Order	Weight	5 Lb Bags	2 Lb Bags
2	1	14	3	0
3	2	1	0	1
4	3	5	1	0
5	4	13	3	0
6	5	11	2	1
7	6	4	1	0
8	7	7	1	1
9	8	8	2	0
10	9	1	0	1
11	10	3	1	0
12	11	6	1	1
13	12	1	0	1
14	13	13	3	0
15	14	2	0	1
16	15	5	1	0
17	16	10	2	0
18	17	9	2	0
19	18	17	3	1
20	19	19	4	0
21	20	6	1	1
22
23			31	9
24			31	9
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=ROUND(B2/5,0)
D2:D21	D2	=(B2>C2*5)+0
C23:D23	C23	=SUM(C2:C21)
C24	C24	=SUMPRODUCT(ROUND(B2:B21/5,0))
D24	D24	=SUMPRODUCT((MOD($B$2:$B$21,5)<3)*(MOD($B$2:$B$21,5)>0))

 

[CafeRoaster]

Hmm. These are great solutions, but I can't seem to get them to work with my sheet. As it is now, I cannot add a column to do the math for each order. I need to be able to do it for an array (specifically, B11:B82), all in one cell.

 

[CafeRoaster]

Here's a test sheet of the range I'm working with.

testing adding bulk bags

Sheet1 11/3/2022,coffee 1,coffee 2,coffee 3,coffee 4,coffee 5,coffee 6,coffee 7 Total 6oz,10,0,0,0,0,0,0 Total 12oz,6,0,0,0,0,0,0 Total 2-lb,3 Total 5-lb,13 Total all BULK lbs below,13.0,0.0,0.0,0.0,0.0,0.0,0.0 Total all 12oz lbs below & UPC,4.5,0.0,0.0,0.0,0.0,0.0,0.0 Total all 6oz lbs below,3....

docs.google.com

 

[CafeRoaster]

I've decided to go with the solution by @Phuoc. I'll create two row next to each column that represents a type of coffee. Those rows will do the math, and I'll hide them so as to not clutter the sheet.

Thank you all!