Help with SUMIFS with multiple conditions

[willow1985]

This is a bit hard to explain so I am including a link to the file in Dropbox as well as a XL2BB in hopes someone could help me with a few SUMIFS formulas or possibly an alternative.

This spreadsheet is a 12 month tracker that will work based on the Date you enter in B3. The Column Months and years will adjust based on the Date entered in B3. That being said the Months and years will change.

The first formula I need can apply to the Demand detail Qty OR the Web EDI Qty (as it will pretty much be the same formula)

It will basically Sum Column C in the "Details" sheet if the part #, Month and Year all Match. I cannot seem to get this formula to work.

The 2nd formula I am not sure how to even start. It is based on the 4 Quarters of the year
Whatever year you enter in B4 will be reflected on top of each Quarters Row.
I would like to Sum the Qty for Jobs for all Months in Q1 for the year listed above for that Row.

I am most likely making this too complicated so could use some help simplifying.

Thank you very much to anyone who can help!

Dropbox link:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1					January	January	January	February	February	February	March	March	March	2021	2021	2021	2021
2					2021	2021	2021	2021	2021	2021	2021	2021	2021
3	Date of Sheet	01-Jan
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	2021 Q1	2021 Q2	2021 Q3	2021 Q4
6	Column1	Column16	Column17	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13	Column14	Column15
7	AXE87725				12	22	22	0	0	0	0	0	0	SUM all Qty for Jobs for Months in Q1, for year in B4,for this row	Same SUM for Q2	Same SUM for Q3	Same SUM for Q4
8	HXE141349				0	0	0	0	31	31	0	36	36	SUM all Qty for Jobs for Months in Q1 for this row	Same SUM for Q2	Same SUM for Q3	Same SUM for Q4
9	QAXE83817C				0	0	0	0	2	2	0	0	0	SUM all Qty for Jobs for Months in Q1 for this row	Same SUM for Q2	Same SUM for Q3	Same SUM for Q4
10	QAXE87825A				0	0	0	0	0	0	0	0	0	SUM all Qty for Jobs for Months in Q1 for this row	Same SUM for Q2	Same SUM for Q3	Same SUM for Q4
11	ZZ554	flag	flag		0	0	0	0		0	0		0
12
13
14
15															Note:
16															Q1	Q2	Q3	Q4
17															January	April	July	October
18															February	May	August	November
19															March	June	September	December
Buckets

Cell Formulas
Range	Formula
E1:G1	E1	=TEXT($B$3,"mmmm")
H1:J1	H1	=EDATE($B$3,1)
K1:M1	K1	=EDATE($B$3,2)
N1:Q1	N1	=$B$4
E2:M2	E2	=TEXT($B$3,"yyyy")
N5	N5	=$B$4&" Q1"
O5	O5	=$B$4&" Q2"
P5	P5	=$B$4&" Q3"
Q5	Q5	=$B$4&" Q4"
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$E$1,Details!E:E,Buckets!$E$2)
F7:F11	F7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!D:D,Buckets!$F$1,'WEB EDI'!E:E,Buckets!$F$2)
G7:G11	G7	=IF([Column3]>[Column4],[Column3],[Column4])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$H$1,Details!E:E,Buckets!$H$2)
I7	I7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$I$1,Details!E:E,Buckets!$I$2)
J7:J11	J7	=IF([Column6]>[Column7],[Column6],[Column7])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$K$1,Details!E:E,Buckets!$K$2)
L7	L7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$L1,Details!E:E,Buckets!$L$2)
M7:M11	M7	=IF([Column9]>[Column10],[Column9],[Column10])

TEST.xlsx
	A	B	C	D	E	F
1	Ship Date	Part Number	Order Qty	Month	Year	Not Present in Buckets
2	10-Jan-21	AXE87725	8	January	2021
3	11-Jan-21	AXE87725	4	January	2021
4	22-Feb-21	HXE141349	5	February	2021
5	17-Mar-21	QAXE83817C	20	March	2021
6	18-Mar-21	QAXE87825A	1	March	2021
7	05-Feb-21	HXE141349	2	February	2021
8	02-Oct	AAAAAB	9	October	2020	flag
Details

Cell Formulas
Range	Formula
D2:D8	D2	=TEXT(A2,"mmmm")
E2:E8	E2	=TEXT(A2,"yyyy")
F2:F8	F2	=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

TEST.xlsx
	A	B	C	D	E	F
1	Ship Date	Part Number	Order Qty	Month	Year	Not Present in Buckets
2	10-Jan-21	AXE87725	20	January	2021
3	11-Jan-21	AXE87725	2	January	2021
4	22-Feb-21	HXE141349	1	February	2021
5	17-Mar-21	QAXE83817C	5	March	2021
6	18-Mar-21	QAXE87825A	7	March	2021
7	05-Feb-21	HXE141349	3	February	2021
8	30-Sep	CCCAT	9	September	2020	flag
WEB EDI

Cell Formulas
Range	Formula
D2:D8	D2	=TEXT(A2,"mmmm")
E2:E8	E2	=TEXT(A2,"yyyy")
F2:F8	F2	=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

 

[Fluff]

How about

TEST.xlsx
	A	B	C	D	E		G	H		J	K		M
1					January		January	February		February	March		March
2					2021		2021	2021		2021	2021		2021
3	Date of Sheet	01-Jan
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty		Qty for JOBS	Demand Detail Qty		Qty for JOBS	Demand Detail Qty		Qty for JOBS
6	Column1	Column16	Column17	Column2	Column3		Column5	Column6		Column8	Column9		Column11
7	AXE87725				12		12	0		0	0		0
8	HXE141349				0		0	7		31	0		36
9	QAXE83817C				0		0	0		2	20		20
10	QAXE87825A				0		0	0		0	1		1
11	ZZ554	flag	flag		0		0	0		0	0		0
Buckets

Cell Formulas
Range	Formula
E1,G1	E1	=$B$3
H1,J1	H1	=EDATE($B$3,1)
K1,M1	K1	=EDATE($B$3,2)
E2,G2:H2,J2:K2,M2	E2	=TEXT($B$3,"yyyy")
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E$1,Details!A:A,"<"&EDATE(E$1,1))
G7:G11	G7	=MAX(Table2[@[Column3]:[Column4]])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$H$1,Details!A:A,"<"&EDATE($H$1,1))
J7:J11	J7	=MAX(Table2[@[Column6]:[Column7]])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$K$1,Details!A:A,"<"&EDATE($K$1,1))
M7:M11	M7	=MAX(Table2[@[Column9]:[Column10]])

Note, I've change the formula in E1:G1.

As you are only showing one quarter, I'm not sure what you mean for part 2

 

[willow1985]

How about

TEST.xlsx
	A	B	C	D	E		G	H		J	K		M
1					January		January	February		February	March		March
2					2021		2021	2021		2021	2021		2021
3	Date of Sheet	01-Jan
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty		Qty for JOBS	Demand Detail Qty		Qty for JOBS	Demand Detail Qty		Qty for JOBS
6	Column1	Column16	Column17	Column2	Column3		Column5	Column6		Column8	Column9		Column11
7	AXE87725				12		12	0		0	0		0
8	HXE141349				0		0	7		31	0		36
9	QAXE83817C				0		0	0		2	20		20
10	QAXE87825A				0		0	0		0	1		1
11	ZZ554	flag	flag		0		0	0		0	0		0
Buckets

Cell Formulas
Range	Formula
E1,G1	E1	=$B$3
H1,J1	H1	=EDATE($B$3,1)
K1,M1	K1	=EDATE($B$3,2)
E2,G2:H2,J2:K2,M2	E2	=TEXT($B$3,"yyyy")
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E$1,Details!A:A,"<"&EDATE(E$1,1))
G7:G11	G7	=MAX(Table2[@[Column3]:[Column4]])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$H$1,Details!A:A,"<"&EDATE($H$1,1))
J7:J11	J7	=MAX(Table2[@[Column6]:[Column7]])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$K$1,Details!A:A,"<"&EDATE($K$1,1))
M7:M11	M7	=MAX(Table2[@[Column9]:[Column10]])

Note, I've change the formula in E1:G1.

As you are only showing one quarter, I'm not sure what you mean for part 2

Yes, I only provided 1 quarter of data just to see what the formula would look like for the Quarters and adjust for the 2, 3rd and 4th quarter accordingly. if there is no data for the remaining quarters it would be blank.

I added another quarter to hopefully paint a better picture of what I am looking for with the totals I am expecting:

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1					January	January	January	February	February	February	March	March	March	April	April	April	May	May	May	June	June	June	2021	2021	2021	2021
2					2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020	2020
3	Date of Sheet	01-Jan
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	2021 Q1	2021 Q2	2021 Q3	2021 Q4
6	Column1	Column16	Column17	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column116	Column117	Column118	Column119	Column112	Column113	Column114	Column1142	Column115	Column12	Column13	Column14	Column15
7	AXE87725				0	0	0	0	0	0	0	0	0	0	0	0	5	2	5	3	0	3	22	8	Same SUM for Q3	Same SUM for Q4
8	HXE141349				0	0	0	0	31	31	0	36	36	0	9	9	0		0	1	2	2	67	11	Same SUM for Q3	Same SUM for Q4
9	QAXE83817C				0	0	0	0	2	2	0	0	0	7	5	7	0	0	0	0	2	2	22	9	Same SUM for Q3	Same SUM for Q4
10	QAXE87825A				0	0	0	0	0	0	0	0	0	2	2	2	0	0	0	1	0	1	1	3	Same SUM for Q3	Same SUM for Q4
11	ZZ554	flag	flag		0	0	0	0		0	0		0	0	1	1	1	0	1	4	2	4	0	6
12
13
14
15																								Note:
16																								Q1	Q2	Q3	Q4
17																								January	April	July	October
18																								February	May	August	November
19																								March	June	September	December
Buckets

Cell Formulas
Range	Formula
E1:G1	E1	=TEXT($B$3,"mmmm")
H1:J1	H1	=EDATE($B$3,1)
K1:M1	K1	=EDATE($B$3,2)
N1:P1	N1	=EDATE($B$3,3)
Q1:S1	Q1	=EDATE($B$3,4)
T1:V1	T1	=EDATE($B$3,5)
W1:Z1	W1	=$B$4
E2:G2	E2	=TEXT($B$3,"yyyy")
H2:V2	H2	=TEXT(H1,"yyyy")
W5	W5	=$B$4&" Q1"
X5	X5	=$B$4&" Q2"
Y5	Y5	=$B$4&" Q3"
Z5	Z5	=$B$4&" Q4"
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E$1,Details!A:A,"<"&EDATE(E$1,1))
F7:F11	F7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!D:D,Buckets!$F$1,'WEB EDI'!E:E,Buckets!$F$2)
G7:G11	G7	=MAX(Table2[@[Column3]:[Column4]])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$H$1,Details!A:A,"<"&EDATE($H$1,1))
I7	I7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$I$1,Details!E:E,Buckets!$I$2)
J7:J11	J7	=MAX(Table2[@[Column6]:[Column7]])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$K$1,Details!A:A,"<"&EDATE($K$1,1))
L7	L7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$L1,Details!E:E,Buckets!$L$2)
M7:M11	M7	=MAX(Table2[@[Column9]:[Column10]])
P7:P11	P7	=MAX(Table2[@[Column116]:[Column117]])
S7:S11	S7	=MAX(Table2[@[Column119]:[Column112]])
V7:V11	V7	=MAX(Table2[@[Column114]:[Column1142]])

 

[willow1985]

It does need to factor in the years and months because there could be a situation where I may have 2 years for example:

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1					November	November	November	December	December	December	January	January	January	February	February	February	March	March	March	April	April	April	2021	2021	2021	2021
2					2020	2020	2020	2020	2020	2020	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021
3	Date of Sheet	02-Nov
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	2021 Q1	2021 Q2	2021 Q3	2021 Q4
6	Column1	Column16	Column17	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column116	Column117	Column118	Column119	Column112	Column113	Column114	Column1142	Column115	Column12	Column13	Column14	Column15
7	AXE87725				0	0	0	0	0	0	12	0	12	0	0	0	5	2	5	3	0	3	17	3	Same SUM for Q3	Same SUM for Q4
8	HXE141349				0	0	0	0	31	31	0	36	36	0	9	9	0		0	1	2	2	45	2	Same SUM for Q3	Same SUM for Q4
9	QAXE83817C				0	0	0	0	2	2	0	0	0	7	5	7	0	0	0	0	2	2	7	2	Same SUM for Q3	Same SUM for Q4
10	QAXE87825A				0	0	0	0	0	0	0	0	0	2	2	2	0	0	0	1	0	1	2	1	Same SUM for Q3	Same SUM for Q4
11	ZZ554	flag	flag		0	0	0	0		0	0		0	0	1	1	1	0	1	4	2	4	2	4
12
13
14
15																								Note:
16																								Q1	Q2	Q3	Q4
17																								January	April	July	October
18																								February	May	August	November
19																								March	June	September	December
20
Buckets

Cell Formulas
Range	Formula
E1:G1	E1	=TEXT($B$3,"mmmm")
H1:J1	H1	=EDATE($B$3,1)
K1:M1	K1	=EDATE($B$3,2)
N1:P1	N1	=EDATE($B$3,3)
Q1:S1	Q1	=EDATE($B$3,4)
T1:V1	T1	=EDATE($B$3,5)
W1:Z1	W1	=$B$4
E2:G2	E2	=TEXT($B$3,"yyyy")
H2:V2	H2	=TEXT(H1,"yyyy")
W5	W5	=$B$4&" Q1"
X5	X5	=$B$4&" Q2"
Y5	Y5	=$B$4&" Q3"
Z5	Z5	=$B$4&" Q4"
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E$1,Details!A:A,"<"&EDATE(E$1,1))
F7:F11	F7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!D:D,Buckets!$F$1,'WEB EDI'!E:E,Buckets!$F$2)
G7:G11	G7	=MAX(Table2[@[Column3]:[Column4]])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$H$1,Details!A:A,"<"&EDATE($H$1,1))
I7	I7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$I$1,Details!E:E,Buckets!$I$2)
J7:J11	J7	=MAX(Table2[@[Column6]:[Column7]])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$K$1,Details!A:A,"<"&EDATE($K$1,1))
L7	L7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!$L1,Details!E:E,Buckets!$L$2)
M7:M11	M7	=MAX(Table2[@[Column9]:[Column10]])
P7:P11	P7	=MAX(Table2[@[Column116]:[Column117]])
S7:S11	S7	=MAX(Table2[@[Column119]:[Column112]])
V7:V11	V7	=MAX(Table2[@[Column114]:[Column1142]])

 

[Fluff]

You need to change the formula in E1:G1, although if the date in B3 is not always the 1st of the month use

=EOMONTH($B$3,-1)+1

and then in H1:J1 use

=EOMONTH($B$3,0)+1

 

[Fluff]

For Q1 use

=SUMIFS(Table2[@[Column3]:[Column120]],$E$5:$V$5,"Qty for jobs",$E$1:$V$1,"<"&DATE($B$4,4,1))

and for Q2

=SUMIFS(Table2[@[Column3]:[Column120]],$E$5:$V$5,"Qty for jobs",$E$1:$V$1,">="&DATE($B$4,4,1),$E$1:$V$1,"<"&DATE($B$4,7,1))

 

[willow1985]

For Q1 use

=SUMIFS(Table2[@[Column3]:[Column120]],$E$5:$V$5,"Qty for jobs",$E$1:$V$1,"<"&DATE($B$4,4,1))

and for Q2

=SUMIFS(Table2[@[Column3]:[Column120]],$E$5:$V$5,"Qty for jobs",$E$1:$V$1,">="&DATE($B$4,4,1),$E$1:$V$1,"<"&DATE($B$4,7,1))

I am getting the following error with both of the formulas for Q1 & Q2.

Also how would I modify the formulas to add in Q3 and Q4?
I updated the sheet with more data if this helps. Thank you again for all of your help!

TEST.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
1					November	November	November	December	December	December	January	January	January	February	February	February	March	March	March	April	April	April	May	May	May	June	June	June	July	July	July	August	August	August	September	September	September	October	October	October	2021	2021	2021	2021
2					2020	2020	2020	2020	2020	2020	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021	2021
3	Date of Sheet	02-Nov
4	Quarter Year	2021
5	Part Number	Detail Flag	Web EDI flag	Notes	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	Demand Detail Qty	Web EDI Qty	Qty for JOBS	2021 Q1	2021 Q2	2021 Q3	2021 Q4
6	Column1	Column16	Column17	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column116	Column117	Column118	Column119	Column112	Column113	Column114	Column1142	Column115	Column1152	Column1153	Column1154	Column1155	Column1156	Column1157	Column1158	Column1159	Column1160	Column1161	Column1162	Column1163	Column1164	Column1165	Column1166	Column1167	Column1168	Column1169	Column12	Column13	Column14	Column15
7	AXE87725				0	0	0	0	0	0	12	22	22	0	0	0	0	0	0	3	1	3	0	2	2	0	0	0	0	0	0	0	0	0	0	0	0	1	0	1	22	5	0	1
8	HXE141349				0	5	5	0	0	0	0	0	0	7	4	7	0	0	0	0	0	0	1	0	1	1	2	2	0	0	0	3	0	3	1	0	1	0	0	0	7	3	4	0
9	QAXE83817C				3	0	3	0	0	0	0	0	0	0	0	0	20	5	20	0	0	0	0	0	0	0	0	0	0	0	0	2	0	2	0	6	6	0	0	0	20	0	8	0
10	QAXE87825A				0	0	0	0	0	0	0	0	0	0	0	0	1	7	7	0	0	0	0	0	0	0	0	0	4	0	4	0	0	0	8	0	8	0	2	2	7	0	12	2
11	ZZ554	flag	flag		0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
12
13
14
15																																										Note:
16																																										Q1	Q2	Q3	Q4
17																																										January	April	July	October
18																																										February	May	August	November
19																																										March	June	September	December
Buckets

Cell Formulas
Range	Formula
E1:G1	E1	=EOMONTH($B$3,-1)+1
H1:J1	H1	=EOMONTH($B$3,0)+1
K1:M1	K1	=EOMONTH($B$3,1)+1
N1:P1	N1	=EOMONTH($B$3,2)+1
Q1:S1	Q1	=EOMONTH($B$3,3)+1
T1:V1	T1	=EOMONTH($B$3,4)+1
W1:Y1	W1	=EOMONTH($B$3,5)+1
Z1:AB1	Z1	=EOMONTH($B$3,6)+1
AC1:AE1	AC1	=EOMONTH($B$3,7)+1
AF1:AH1	AF1	=EOMONTH($B$3,8)+1
AI1:AK1	AI1	=EOMONTH($B$3,9)+1
AL1:AN1	AL1	=EOMONTH($B$3,10)+1
AO1:AR1	AO1	=$B$4
E2:G2	E2	=TEXT($B$3,"yyyy")
H2:V2	H2	=TEXT(H1,"yyyy")
AO5	AO5	=$B$4&" Q1"
AP5	AP5	=$B$4&" Q2"
AQ5	AQ5	=$B$4&" Q3"
AR5	AR5	=$B$4&" Q4"
B7:B11	B7	=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11	C7	=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11	E7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E$1,Details!A:A,"<"&EDATE(E$1,1))
F7:F11	F7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&F$1,'WEB EDI'!A:A,"<"&EDATE(F$1,1))
G7:G11	G7	=MAX(Table2[@[Column3]:[Column4]])
H7:H11	H7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$H$1,Details!A:A,"<"&EDATE($H$1,1))
I7:I11	I7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&I$1,'WEB EDI'!A:A,"<"&EDATE(I$1,1))
J7:J11	J7	=MAX(Table2[@[Column6]:[Column7]])
K7:K11	K7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&$K$1,Details!A:A,"<"&EDATE($K$1,1))
L7:L11	L7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&L$1,'WEB EDI'!A:A,"<"&EDATE(L$1,1))
M7:M11	M7	=MAX(Table2[@[Column9]:[Column10]])
N7:N11	N7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&N$1,Details!A:A,"<"&EDATE(N$1,1))
O7:O11	O7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&O$1,'WEB EDI'!A:A,"<"&EDATE(O$1,1))
P7:P11	P7	=MAX(Table2[@[Column116]:[Column117]])
Q7:Q11	Q7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&Q$1,Details!A:A,"<"&EDATE(Q$1,1))
R7:R11	R7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&R$1,'WEB EDI'!A:A,"<"&EDATE(R$1,1))
S7:S11	S7	=MAX(Table2[@[Column119]:[Column112]])
T7:T11	T7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&T$1,Details!A:A,"<"&EDATE(T$1,1))
U7:U11	U7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&U$1,'WEB EDI'!A:A,"<"&EDATE(U$1,1))
V7:V11	V7	=MAX(Table2[@[Column114]:[Column1142]])
W7:W11	W7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&W$1,Details!A:A,"<"&EDATE(W$1,1))
X7:X11	X7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&X$1,'WEB EDI'!A:A,"<"&EDATE(X$1,1))
Y7:Y11	Y7	=MAX(Table2[@[Column1152]:[Column1153]])
Z7:Z11	Z7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&Y$1,Details!A:A,"<"&EDATE(Y$1,1))
AA7:AA11	AA7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&AA$1,'WEB EDI'!A:A,"<"&EDATE(AA$1,1))
AB7:AB11	AB7	=MAX(Table2[@[Column1155]:[Column1156]])
AC7:AC11	AC7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AC$1,Details!A:A,"<"&EDATE(AC$1,1))
AD7:AD11	AD7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&AD$1,'WEB EDI'!A:A,"<"&EDATE(AD$1,1))
AE7:AE11	AE7	=MAX(Table2[@[Column1158]:[Column1159]])
AF7:AF11	AF7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AF$1,Details!A:A,"<"&EDATE(AF$1,1))
AG7:AG11	AG7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&AG$1,'WEB EDI'!A:A,"<"&EDATE(AG$1,1))
AH7:AH11	AH7	=MAX(Table2[@[Column1161]:[Column1162]])
AI7:AI11	AI7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AI$1,Details!A:A,"<"&EDATE(AI$1,1))
AJ7:AJ11	AJ7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&AJ$1,'WEB EDI'!A:A,"<"&EDATE(AJ$1,1))
AK7:AK11	AK7	=MAX(Table2[@[Column1164]:[Column1165]])
AL7:AL11	AL7	=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AL$1,Details!A:A,"<"&EDATE(AL$1,1))
AM7:AM11	AM7	=SUMIFS('WEB EDI'!C:C,'WEB EDI'!B:B,[@Column1],'WEB EDI'!A:A,">="&AM$1,'WEB EDI'!A:A,"<"&EDATE(AM$1,1))
AN7:AN11	AN7	=MAX(Table2[@[Column1167]:[Column1168]])

TEST.xlsx
	A	B	C	D	E	F
1	Ship Date	Part Number	Order Qty	Month	Year	Not Present in Buckets
2	10-Jan-21	AXE87725	8	January	2021
3	11-Jan-21	AXE87725	4	January	2021
4	22-Feb-21	HXE141349	5	February	2021
5	08-Nov-20	QAXE83817C	3	November	2020
6	17-Mar-21	QAXE83817C	20	March	2021
7	18-Mar-21	QAXE87825A	1	March	2021
8	05-Feb-21	HXE141349	2	February	2021
9	02-Oct	AAAAAB	9	October	2021	flag
10	01-Apr	AXE87725	2	April	2021
11	20-Apr	AXE87725	1	April	2021
12	17-May	HXE141349	1	May	2021
13	22-Jun	QAXE83817C	6	June	2021
14	04-Jul	QAXE87825A	4	July	2021
15	01-Aug	HXE141349	3	August	2021
16	16-Aug	QAXE83817C	2	August	2021
17	08-Sep	QAXE87825A	8	September	2021
18	07-Sep	HXE141349	1	September	2021
19	09-Oct	AXE87725	1	October	2021
20	03-Nov	AXE87725	3	November	2021
21	30-Dec	HXE141349	2	December	2021
Details

Cell Formulas
Range	Formula
D2:D21	D2	=TEXT(A2,"mmmm")
E2:E21	E2	=TEXT(A2,"yyyy")
F6:F21,F2:F4	F2	=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

TEST.xlsx
	A	B	C	D	E	F
1	Ship Date	Part Number	Order Qty	Month	Year	Not Present in Buckets
2	10-Jan-21	AXE87725	20	January	2021
3	11-Jan-21	AXE87725	2	January	2021
4	02-Nov-20	HXE141349	5	November	2020
5	22-Feb-21	HXE141349	1	February	2021
6	17-Mar-21	QAXE83817C	5	March	2021
7	18-Mar-21	QAXE87825A	7	March	2021
8	05-Feb-21	HXE141349	3	February	2021
9	30-Sep	CCCAT	9	September	2021	flag
10	20-Apr	AXE87725	1	April	2021
11	17-May	AXE87725	2	May	2021
12	22-Jun	HXE141349	2	June	2021
13	10-Sep	QAXE83817C	6	September	2021
14	09-Oct	QAXE87825A	1	October	2021
15	03-Nov	HXE141349	3	November	2021
16	12-Dec	QAXE83817C	2	December	2021
17	08-Oct	QAXE87825A	1	October	2021
18	15-Nov	HXE141349	1	November	2021
WEB EDI

Cell Formulas
Range	Formula
D2:D18	D2	=TEXT(A2,"mmmm")
E2:E18	E2	=TEXT(A2,"yyyy")
F5:F18,F2:F3	F2	=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

 

[Fluff]

With the new layout it needs to be

=SUMIFS(Table2[@[Column3]:[Column1169]],$E$5:$AN$5,"Qty for jobs",$E$1:$AN$1,">="&DATE($B$4,1,1),$E$1:$AN$1,"<"&DATE($B$4,4,1))
=SUMIFS(Table2[@[Column3]:[Column1169]],$E$5:$AN$5,"Qty for jobs",$E$1:$AN$1,">="&DATE($B$4,4,1),$E$1:$AN$1,"<"&DATE($B$4,7,1))

 

[willow1985]

With the new layout it needs to be

=SUMIFS(Table2[@[Column3]:[Column1169]],$E$5:$AN$5,"Qty for jobs",$E$1:$AN$1,">="&DATE($B$4,1,1),$E$1:$AN$1,"<"&DATE($B$4,4,1))
=SUMIFS(Table2[@[Column3]:[Column1169]],$E$5:$AN$5,"Qty for jobs",$E$1:$AN$1,">="&DATE($B$4,4,1),$E$1:$AN$1,"<"&DATE($B$4,7,1))

I see why the DATE($B4,1,1) formula is important now, very smart!

Thank you very much this will work perfectly!

 

[Fluff]

You're welcome & thanks for the feedback.