Sum if the adjacent cell is same as a cell in sperate tab AND another cell in separate tab is not empty

[Woodpusher147]

HI and thank you for any help given

IM sure its not to complex but I need help

IM looking for a formula to place in C5,6,7 etc (Tab 2) that will Sum cells in column C (Tab1) that contain the same code (L, M, XL) as column B (Tab2) This should only calculate IF J13 (Tab1) is not empty

This is TAB 2 where I need it to calculate the amount of L,M etc shirts that are black or white

Tab 1 is the order form
which is a little like this

There are also the 2 cells with black or white choice. An X would be placed in either depending which colour. All orders per order form are the same colour choice





IT really doesnt matter which cells these are, they can be A B C as these will change a little




Thanks again for any help

 

[Woodpusher147]

By addiing the extra columns to siginify which are white & which are black.

Really sorry but I dont understand

Could you give me an example?

I guess it would be better to take the results from each individual Order sheet yes?

 

[Fluff]

In that case can you post the data from the consolidated sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Woodpusher147]

Here is the COllation sheet

Dunamis Cutting Plan List.xlsx
	B	C	D	E	F	G	H
6	Order 1				Master Cut List
7	Jersey Style	Size	Quantity
8	110	XL	30
9	Jersey #001	J13-	25		Size	White	Black
10	Jersey #002	S	10		Baby	0
11	Jersey #004	XL	40		5XS	0
12	Jersey	2XLNM	20		4XS	0
13	0	0	0		3XS	0
14	0	0	0		2XS	0
15	0	0	0		XS	0
16			125		S	57
17	Order 2				M	0
18	Jersey Style	Size	Quantity		L	79
19	Jersey #003	XL	15		XL	185
20	Jersey #001	L	25		2XL	0
21	Jersey #002	S	10		3XL	0
22	0	0	0		4XL	0
23	0	0	0		5XL	0
24	0	0	0		Netminder Jersey Cuts
25	0	0	0
26	0	0	0		SNM	0
27					MNM	0
28	Order 3				LMN	0
29	Jersey Style	Size	Quantity		XLNM	0
30	Jersey #003	XL	15		2XLNM	20
31	Jersey #001	L	25
32	Jersey #001	S	10
33	0	0	0
34	0	0	0
35	0	0	0
36	0	0	0
37	0	0	0
38
39	Order 4
40	Jersey Style	Size	Quantity
41	Jersey #003	XL	15
42	Jersey #003	L	4
43	Jersey #001	S	17
44	0	0	0
45	0	0	0
46	0	0	0
47	0	0	0
48	0	0	0
49
50	Order 5
51	Jersey Style	Size	Quantity
52	Jersey #003	XL	30
53	Jersey #001	L	25
54	Jersey #002	S	10
55	Jersey #004	XL	40
56	0	0	0
57	0	0	0
58	0	0	0
59	0	0	0
Collation

Cell Formulas
Range	Formula
B8:D15	B8	='Order 1'!B43:D50
D16	D16	=SUM(D8:D15)
B19:D26	B19	='Order 2'!B43:D50
G10	G10	=J16
G11:G23,G26:G30	G11	=SUM(IF($C$8:$C$59=F11, $D$8:$D$59))
B30:D37	B30	='Order 3'!B43:D50
B41:D48	B41	='Order 4'!B43:D50
B52:D59	B52	='Order 5'!B43:D50
Dynamic array formulas.

 

[Woodpusher147]

Here is the COllation sheet

Dunamis Cutting Plan List.xlsx
	B	C	D	E	F	G	H
6	Order 1				Master Cut List
7	Jersey Style	Size	Quantity
8	110	XL	30
9	Jersey #001	J13-	25		Size	White	Black
10	Jersey #002	S	10		Baby	0
11	Jersey #004	XL	40		5XS	0
12	Jersey	2XLNM	20		4XS	0
13	0	0	0		3XS	0
14	0	0	0		2XS	0
15	0	0	0		XS	0
16			125		S	57
17	Order 2				M	0
18	Jersey Style	Size	Quantity		L	79
19	Jersey #003	XL	15		XL	185
20	Jersey #001	L	25		2XL	0
21	Jersey #002	S	10		3XL	0
22	0	0	0		4XL	0
23	0	0	0		5XL	0
24	0	0	0		Netminder Jersey Cuts
25	0	0	0
26	0	0	0		SNM	0
27					MNM	0
28	Order 3				LMN	0
29	Jersey Style	Size	Quantity		XLNM	0
30	Jersey #003	XL	15		2XLNM	20
31	Jersey #001	L	25
32	Jersey #001	S	10
33	0	0	0
34	0	0	0
35	0	0	0
36	0	0	0
37	0	0	0
38
39	Order 4
40	Jersey Style	Size	Quantity
41	Jersey #003	XL	15
42	Jersey #003	L	4
43	Jersey #001	S	17
44	0	0	0
45	0	0	0
46	0	0	0
47	0	0	0
48	0	0	0
49
50	Order 5
51	Jersey Style	Size	Quantity
52	Jersey #003	XL	30
53	Jersey #001	L	25
54	Jersey #002	S	10
55	Jersey #004	XL	40
56	0	0	0
57	0	0	0
58	0	0	0
59	0	0	0
Collation

Cell Formulas
Range	Formula
B8:D15	B8	='Order 1'!B43:D50
D16	D16	=SUM(D8:D15)
B19:D26	B19	='Order 2'!B43:D50
G10	G10	=J16
G11:G23,G26:G30	G11	=SUM(IF($C$8:$C$59=F11, $D$8:$D$59))
B30:D37	B30	='Order 3'!B43:D50
B41:D48	B41	='Order 4'!B43:D50
B52:D59	B52	='Order 5'!B43:D50
Dynamic array formulas.

And here is one of the 5 Order sheets, they are all the same but each one would only have one of the Black or White cells marked with an X as all the shorts in each sheet would be same colour

Dunamis Cutting Plan List.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2				Production Works Order			Ref:		Page 1 of
3							Delivery Address
4	Delivery Due (W/C):
5
6	Customer Order Ref:
7
8	Priority:
9
10	Copy of Customer Order Attached? Y/N
11	Specification/Artwork - Customer Approval Form must be attached									Cut Style	Neck Type
12										WHITE	U' Cut
13										X
14										BLACK	V' Cut
15
16										Dress Cut	Diamond
17
18										Std. Ladies	Inner Neck Colour
19
20										Player Ladies
21
22										Stitch Colour	Ribbon Colour
23										Overlock	Black
24
25										Hem	Navy
26
27										Cover	Neck
28
29										Collar	Lace
30
31
32	Materials/Parts Required i.e PW314, PW350
33	Awaiting Ordered		Awaiting Due		Date arrived		All Parts in Stock		Stock used Y/N
34
35	Special Instructions						Delivery Instructions
36
37
38
39	Print Profile
40	6000			6200/9400
41
42	Tray No.	Product	Size	Quantity	Date / Start Time	Initial Check	Cutting Check	Printing Check	Heat Press Check	Sewing Check	Packing Check
43	Z1	Jersey #003	XL	15	03/01/2023
44	Z2	Jersey #001	L	25
45	Z3	Jersey #001	S	10
46	Z4
47	Z5
48
49
Order 3

 

[Fluff]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
4
5
6		Order 1							Master Cut List
7		Jersey Style	Size	Quantity	Black	White
8		110	XL	30		x
9		Jersey #001	J13-	25		x			Size	White	Black
10		Jersey #002	S	10		x			Baby	0	0
11		Jersey #004	XL	40		x			5XS	0	0
12		Jersey	2XLNM	20		x			4XS	0	0
13		0	0	0		x			3XS	0	0
14		0	0	0		x			2XS	0	0
15		0	0	0		x			XS	0	0
16				125					S	10	10
17		Order 2							M	0	0
18		Jersey Style	Size	Quantity					L	0	25
19		Jersey #003	XL	15	x				XL	70	15
20		Jersey #001	L	25	x				2XL	0	0
21		Jersey #002	S	10	x				3XL	0	0
22		0	0	0	x				4XL	0	0
23		0	0	0	x				5XL	0	0
24		0	0	0	x				Netminder Jersey Cuts
25		0	0	0	x
Main

Cell Formulas
Range	Formula
J10:J23	J10	=SUMIFS($D$8:$D$59,$C$8:$C$59,I10,$F$8:$F$59,"x")
K10:K23	K10	=SUMIFS($D$8:$D$59,$C$8:$C$59,I10,$E$8:$E$59,"x")
F8:F15	F8	=Order1!$J$16&""
E8:E15	E8	=Order1!$J$18&""
E16	E16	=Order1!J26&""
F19	F19	=Order2!$J$16&""
E19:E25	E19	=Order2!$J$18&""

Change the cell refs in cols E & F to suit

 

[Woodpusher147]

Thanks for that, how about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
4
5
6		Order 1							Master Cut List
7		Jersey Style	Size	Quantity	Black	White
8		110	XL	30		x
9		Jersey #001	J13-	25		x			Size	White	Black
10		Jersey #002	S	10		x			Baby	0	0
11		Jersey #004	XL	40		x			5XS	0	0
12		Jersey	2XLNM	20		x			4XS	0	0
13		0	0	0		x			3XS	0	0
14		0	0	0		x			2XS	0	0
15		0	0	0		x			XS	0	0
16				125					S	10	10
17		Order 2							M	0	0
18		Jersey Style	Size	Quantity					L	0	25
19		Jersey #003	XL	15	x				XL	70	15
20		Jersey #001	L	25	x				2XL	0	0
21		Jersey #002	S	10	x				3XL	0	0
22		0	0	0	x				4XL	0	0
23		0	0	0	x				5XL	0	0
24		0	0	0	x				Netminder Jersey Cuts
25		0	0	0	x
Main

Cell Formulas
Range	Formula
J10:J23	J10	=SUMIFS($D$8:$D$59,$C$8:$C$59,I10,$F$8:$F$59,"x")
K10:K23	K10	=SUMIFS($D$8:$D$59,$C$8:$C$59,I10,$E$8:$E$59,"x")
F8:F15	F8	=Order1!$J$16&""
E8:E15	E8	=Order1!$J$18&""
E16	E16	=Order1!J26&""
F19	F19	=Order2!$J$16&""
E19:E25	E19	=Order2!$J$18&""

Change the cell refs in cols E & F to suit

THIS IS GREAT

THANK YOU VERY MUCH

 

[Fluff]

You're welcome & thanks for the feedback.