# Help with SUMIFS with multiple conditions

#### willow1985

##### Well-known Member
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!

TEST.xlsx
ABCDEFGHIJKLMNOPQR
1JanuaryJanuaryJanuaryFebruaryFebruaryFebruaryMarchMarchMarch2021202120212021
2202120212021202120212021202120212021
3Date of Sheet01-Jan
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBS2021 Q12021 Q22021 Q32021 Q4
6Column1Column16Column17Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column12Column13Column14Column15
7AXE87725  122222000000SUM all Qty for Jobs for Months in Q1, for year in B4,for this rowSame SUM for Q2Same SUM for Q3Same SUM for Q4
8HXE141349  0000313103636SUM all Qty for Jobs for Months in Q1 for this rowSame SUM for Q2Same SUM for Q3Same SUM for Q4
9QAXE83817C  000022000SUM all Qty for Jobs for Months in Q1 for this rowSame SUM for Q2Same SUM for Q3Same SUM for Q4
10QAXE87825A  000000000SUM all Qty for Jobs for Months in Q1 for this rowSame SUM for Q2Same SUM for Q3Same SUM for Q4
11ZZ554flagflag0000000
12
13
14
15Note:
16Q1Q2Q3Q4
17JanuaryAprilJulyOctober
18FebruaryMayAugustNovember
19MarchJuneSeptemberDecember
Buckets
Cell Formulas
RangeFormula
E1:G1E1=TEXT(\$B\$3,"mmmm")
H1:J1H1=EDATE(\$B\$3,1)
K1:M1K1=EDATE(\$B\$3,2)
N1:Q1N1=\$B\$4
E2:M2E2=TEXT(\$B\$3,"yyyy")
N5N5=\$B\$4&" Q1"
O5O5=\$B\$4&" Q2"
P5P5=\$B\$4&" Q3"
Q5Q5=\$B\$4&" Q4"
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$E\$1,Details!E:E,Buckets!\$E\$2)
F7:F11F7=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:G11G7=IF([Column3]>[Column4],[Column3],[Column4])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$H\$1,Details!E:E,Buckets!\$H\$2)
I7I7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$I\$1,Details!E:E,Buckets!\$I\$2)
J7:J11J7=IF([Column6]>[Column7],[Column6],[Column7])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$K\$1,Details!E:E,Buckets!\$K\$2)
L7L7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$L1,Details!E:E,Buckets!\$L\$2)
M7:M11M7=IF([Column9]>[Column10],[Column9],[Column10])

TEST.xlsx
ABCDEF
1Ship DatePart NumberOrder QtyMonthYearNot Present in Buckets
210-Jan-21AXE877258January2021
311-Jan-21AXE877254January2021
422-Feb-21HXE1413495February2021
517-Mar-21QAXE83817C20March2021
618-Mar-21QAXE87825A1March2021
705-Feb-21HXE1413492February2021
802-OctAAAAAB9October2020flag
Details
Cell Formulas
RangeFormula
D2:D8D2=TEXT(A2,"mmmm")
E2:E8E2=TEXT(A2,"yyyy")
F2:F8F2=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

TEST.xlsx
ABCDEF
1Ship DatePart NumberOrder QtyMonthYearNot Present in Buckets
210-Jan-21AXE8772520January2021
311-Jan-21AXE877252January2021
422-Feb-21HXE1413491February2021
517-Mar-21QAXE83817C5March2021
618-Mar-21QAXE87825A7March2021
705-Feb-21HXE1413493February2021
830-SepCCCAT9September2020flag
WEB EDI
Cell Formulas
RangeFormula
D2:D8D2=TEXT(A2,"mmmm")
E2:E8E2=TEXT(A2,"yyyy")
F2:F8F2=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Fluff

##### MrExcel MVP, Moderator
TEST.xlsx
ABCDEGHJKM
1JanuaryJanuaryFebruaryFebruaryMarchMarch
2202120212021202120212021
3Date of Sheet01-Jan
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyQty for JOBSDemand Detail QtyQty for JOBSDemand Detail QtyQty for JOBS
6Column1Column16Column17Column2Column3Column5Column6Column8Column9Column11
7AXE87725  12120000
8HXE141349  00731036
9QAXE83817C  00022020
10QAXE87825A  000011
11ZZ554flagflag000000
Buckets
Cell Formulas
RangeFormula
E1,G1E1=\$B\$3
H1,J1H1=EDATE(\$B\$3,1)
K1,M1K1=EDATE(\$B\$3,2)
E2,G2:H2,J2:K2,M2E2=TEXT(\$B\$3,"yyyy")
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E\$1,Details!A:A,"<"&EDATE(E\$1,1))
G7:G11G7=MAX(Table2[@[Column3]:[Column4]])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$H\$1,Details!A:A,"<"&EDATE(\$H\$1,1))
J7:J11J7=MAX(Table2[@[Column6]:[Column7]])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$K\$1,Details!A:A,"<"&EDATE(\$K\$1,1))
M7:M11M7=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

Last edited:

#### willow1985

##### Well-known Member
TEST.xlsx
ABCDEGHJKM
1JanuaryJanuaryFebruaryFebruaryMarchMarch
2202120212021202120212021
3Date of Sheet01-Jan
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyQty for JOBSDemand Detail QtyQty for JOBSDemand Detail QtyQty for JOBS
6Column1Column16Column17Column2Column3Column5Column6Column8Column9Column11
7AXE87725  12120000
8HXE141349  00731036
9QAXE83817C  00022020
10QAXE87825A  000011
11ZZ554flagflag000000
Buckets
Cell Formulas
RangeFormula
E1,G1E1=\$B\$3
H1,J1H1=EDATE(\$B\$3,1)
K1,M1K1=EDATE(\$B\$3,2)
E2,G2:H2,J2:K2,M2E2=TEXT(\$B\$3,"yyyy")
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E\$1,Details!A:A,"<"&EDATE(E\$1,1))
G7:G11G7=MAX(Table2[@[Column3]:[Column4]])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$H\$1,Details!A:A,"<"&EDATE(\$H\$1,1))
J7:J11J7=MAX(Table2[@[Column6]:[Column7]])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$K\$1,Details!A:A,"<"&EDATE(\$K\$1,1))
M7:M11M7=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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1JanuaryJanuaryJanuaryFebruaryFebruaryFebruaryMarchMarchMarchAprilAprilAprilMayMayMayJuneJuneJune2021202120212021
2202020202020202020202020202020202020202020202020202020202020202020202020
3Date of Sheet01-Jan
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBS2021 Q12021 Q22021 Q32021 Q4
6Column1Column16Column17Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column116Column117Column118Column119Column112Column113Column114Column1142Column115Column12Column13Column14Column15
7AXE87725  000000000000525303228Same SUM for Q3Same SUM for Q4
8HXE141349  0000313103636099001226711Same SUM for Q3Same SUM for Q4
9QAXE83817C  000022000757000022229Same SUM for Q3Same SUM for Q4
10QAXE87825A  00000000022200010113Same SUM for Q3Same SUM for Q4
11ZZ554flagflag000000001110142406
12
13
14
15Note:
16Q1Q2Q3Q4
17JanuaryAprilJulyOctober
18FebruaryMayAugustNovember
19MarchJuneSeptemberDecember
Buckets
Cell Formulas
RangeFormula
E1:G1E1=TEXT(\$B\$3,"mmmm")
H1:J1H1=EDATE(\$B\$3,1)
K1:M1K1=EDATE(\$B\$3,2)
N1:P1N1=EDATE(\$B\$3,3)
Q1:S1Q1=EDATE(\$B\$3,4)
T1:V1T1=EDATE(\$B\$3,5)
W1:Z1W1=\$B\$4
E2:G2E2=TEXT(\$B\$3,"yyyy")
H2:V2H2=TEXT(H1,"yyyy")
W5W5=\$B\$4&" Q1"
X5X5=\$B\$4&" Q2"
Y5Y5=\$B\$4&" Q3"
Z5Z5=\$B\$4&" Q4"
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E\$1,Details!A:A,"<"&EDATE(E\$1,1))
F7:F11F7=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:G11G7=MAX(Table2[@[Column3]:[Column4]])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$H\$1,Details!A:A,"<"&EDATE(\$H\$1,1))
I7I7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$I\$1,Details!E:E,Buckets!\$I\$2)
J7:J11J7=MAX(Table2[@[Column6]:[Column7]])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$K\$1,Details!A:A,"<"&EDATE(\$K\$1,1))
L7L7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$L1,Details!E:E,Buckets!\$L\$2)
M7:M11M7=MAX(Table2[@[Column9]:[Column10]])
P7:P11P7=MAX(Table2[@[Column116]:[Column117]])
S7:S11S7=MAX(Table2[@[Column119]:[Column112]])
V7:V11V7=MAX(Table2[@[Column114]:[Column1142]])

Last edited:

#### willow1985

##### Well-known Member
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1NovemberNovemberNovemberDecemberDecemberDecemberJanuaryJanuaryJanuaryFebruaryFebruaryFebruaryMarchMarchMarchAprilAprilApril2021202120212021
2202020202020202020202020202120212021202120212021202120212021202120212021
3Date of Sheet02-Nov
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBS2021 Q12021 Q22021 Q32021 Q4
6Column1Column16Column17Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column116Column117Column118Column119Column112Column113Column114Column1142Column115Column12Column13Column14Column15
7AXE87725  00000012012000525303173Same SUM for Q3Same SUM for Q4
8HXE141349  000031310363609900122452Same SUM for Q3Same SUM for Q4
9QAXE83817C  00002200075700002272Same SUM for Q3Same SUM for Q4
10QAXE87825A  00000000022200010121Same SUM for Q3Same SUM for Q4
11ZZ554flagflag000000001110142424
12
13
14
15Note:
16Q1Q2Q3Q4
17JanuaryAprilJulyOctober
18FebruaryMayAugustNovember
19MarchJuneSeptemberDecember
20
Buckets
Cell Formulas
RangeFormula
E1:G1E1=TEXT(\$B\$3,"mmmm")
H1:J1H1=EDATE(\$B\$3,1)
K1:M1K1=EDATE(\$B\$3,2)
N1:P1N1=EDATE(\$B\$3,3)
Q1:S1Q1=EDATE(\$B\$3,4)
T1:V1T1=EDATE(\$B\$3,5)
W1:Z1W1=\$B\$4
E2:G2E2=TEXT(\$B\$3,"yyyy")
H2:V2H2=TEXT(H1,"yyyy")
W5W5=\$B\$4&" Q1"
X5X5=\$B\$4&" Q2"
Y5Y5=\$B\$4&" Q3"
Z5Z5=\$B\$4&" Q4"
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E\$1,Details!A:A,"<"&EDATE(E\$1,1))
F7:F11F7=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:G11G7=MAX(Table2[@[Column3]:[Column4]])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$H\$1,Details!A:A,"<"&EDATE(\$H\$1,1))
I7I7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$I\$1,Details!E:E,Buckets!\$I\$2)
J7:J11J7=MAX(Table2[@[Column6]:[Column7]])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$K\$1,Details!A:A,"<"&EDATE(\$K\$1,1))
L7L7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!D:D,Buckets!\$L1,Details!E:E,Buckets!\$L\$2)
M7:M11M7=MAX(Table2[@[Column9]:[Column10]])
P7:P11P7=MAX(Table2[@[Column116]:[Column117]])
S7:S11S7=MAX(Table2[@[Column119]:[Column112]])
V7:V11V7=MAX(Table2[@[Column114]:[Column1142]])

#### Fluff

##### MrExcel MVP, Moderator

You need to change the formula in E1:G1, although if the date in B3 is not always the 1st of the month use
Excel Formula:
``=EOMONTH(\$B\$3,-1)+1``
and then in H1:J1 use
Excel Formula:
``=EOMONTH(\$B\$3,0)+1``

#### Fluff

##### MrExcel MVP, Moderator
For Q1 use
Excel Formula:
``=SUMIFS(Table2[@[Column3]:[Column120]],\$E\$5:\$V\$5,"Qty for jobs",\$E\$1:\$V\$1,"<"&DATE(\$B\$4,4,1))``
and for Q2
Excel Formula:
``=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

##### Well-known Member

For Q1 use
Excel Formula:
``=SUMIFS(Table2[@[Column3]:[Column120]],\$E\$5:\$V\$5,"Qty for jobs",\$E\$1:\$V\$1,"<"&DATE(\$B\$4,4,1))``
and for Q2
Excel Formula:
``=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
1NovemberNovemberNovemberDecemberDecemberDecemberJanuaryJanuaryJanuaryFebruaryFebruaryFebruaryMarchMarchMarchAprilAprilAprilMayMayMayJuneJuneJuneJulyJulyJulyAugustAugustAugustSeptemberSeptemberSeptemberOctoberOctoberOctober2021202120212021
2202020202020202020202020202120212021202120212021202120212021202120212021
3Date of Sheet02-Nov
4Quarter Year2021
5Part NumberDetail FlagWeb EDI flagNotesDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBSDemand Detail QtyWeb EDI QtyQty for JOBS2021 Q12021 Q22021 Q32021 Q4
6Column1Column16Column17Column2Column3Column4Column5Column6Column7Column8Column9Column10Column11Column116Column117Column118Column119Column112Column113Column114Column1142Column115Column1152Column1153Column1154Column1155Column1156Column1157Column1158Column1159Column1160Column1161Column1162Column1163Column1164Column1165Column1166Column1167Column1168Column1169Column12Column13Column14Column15
7AXE87725  00000012222200000031302200000000000010122501
8HXE141349  0550000007470000001011220003031010007340
9QAXE83817C  3030000000002052000000000000020206600020080
10QAXE87825A  00000000000017700000000040400080802270122
11ZZ554flagflag0000000000000000000000000000000000000000
12
13
14
15Note:
16Q1Q2Q3Q4
17JanuaryAprilJulyOctober
18FebruaryMayAugustNovember
19MarchJuneSeptemberDecember
Buckets
Cell Formulas
RangeFormula
E1:G1E1=EOMONTH(\$B\$3,-1)+1
H1:J1H1=EOMONTH(\$B\$3,0)+1
K1:M1K1=EOMONTH(\$B\$3,1)+1
N1:P1N1=EOMONTH(\$B\$3,2)+1
Q1:S1Q1=EOMONTH(\$B\$3,3)+1
T1:V1T1=EOMONTH(\$B\$3,4)+1
W1:Y1W1=EOMONTH(\$B\$3,5)+1
Z1:AB1Z1=EOMONTH(\$B\$3,6)+1
AC1:AE1AC1=EOMONTH(\$B\$3,7)+1
AF1:AH1AF1=EOMONTH(\$B\$3,8)+1
AI1:AK1AI1=EOMONTH(\$B\$3,9)+1
AL1:AN1AL1=EOMONTH(\$B\$3,10)+1
AO1:AR1AO1=\$B\$4
E2:G2E2=TEXT(\$B\$3,"yyyy")
H2:V2H2=TEXT(H1,"yyyy")
AO5AO5=\$B\$4&" Q1"
AP5AP5=\$B\$4&" Q2"
AQ5AQ5=\$B\$4&" Q3"
AR5AR5=\$B\$4&" Q4"
B7:B11B7=IF(ISERROR(VLOOKUP(A7,Details!B:B,1,FALSE)),"flag","")
C7:C11C7=IF(ISERROR(VLOOKUP(A7,'WEB EDI'!B:B,1,FALSE)),"flag","")
E7:E11E7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&E\$1,Details!A:A,"<"&EDATE(E\$1,1))
F7:F11F7=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:G11G7=MAX(Table2[@[Column3]:[Column4]])
H7:H11H7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$H\$1,Details!A:A,"<"&EDATE(\$H\$1,1))
I7:I11I7=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:J11J7=MAX(Table2[@[Column6]:[Column7]])
K7:K11K7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&\$K\$1,Details!A:A,"<"&EDATE(\$K\$1,1))
L7:L11L7=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:M11M7=MAX(Table2[@[Column9]:[Column10]])
N7:N11N7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&N\$1,Details!A:A,"<"&EDATE(N\$1,1))
O7:O11O7=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:P11P7=MAX(Table2[@[Column116]:[Column117]])
Q7:Q11Q7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&Q\$1,Details!A:A,"<"&EDATE(Q\$1,1))
R7:R11R7=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:S11S7=MAX(Table2[@[Column119]:[Column112]])
T7:T11T7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&T\$1,Details!A:A,"<"&EDATE(T\$1,1))
U7:U11U7=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:V11V7=MAX(Table2[@[Column114]:[Column1142]])
W7:W11W7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&W\$1,Details!A:A,"<"&EDATE(W\$1,1))
X7:X11X7=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:Y11Y7=MAX(Table2[@[Column1152]:[Column1153]])
Z7:Z11Z7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&Y\$1,Details!A:A,"<"&EDATE(Y\$1,1))
AA7:AA11AA7=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:AB11AB7=MAX(Table2[@[Column1155]:[Column1156]])
AC7:AC11AC7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AC\$1,Details!A:A,"<"&EDATE(AC\$1,1))
AE7:AE11AE7=MAX(Table2[@[Column1158]:[Column1159]])
AF7:AF11AF7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AF\$1,Details!A:A,"<"&EDATE(AF\$1,1))
AG7:AG11AG7=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:AH11AH7=MAX(Table2[@[Column1161]:[Column1162]])
AI7:AI11AI7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AI\$1,Details!A:A,"<"&EDATE(AI\$1,1))
AJ7:AJ11AJ7=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:AK11AK7=MAX(Table2[@[Column1164]:[Column1165]])
AL7:AL11AL7=SUMIFS(Details!C:C,Details!B:B,[@Column1],Details!A:A,">="&AL\$1,Details!A:A,"<"&EDATE(AL\$1,1))
AM7:AM11AM7=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:AN11AN7=MAX(Table2[@[Column1167]:[Column1168]])

TEST.xlsx
ABCDEF
1Ship DatePart NumberOrder QtyMonthYearNot Present in Buckets
210-Jan-21AXE877258January2021
311-Jan-21AXE877254January2021
422-Feb-21HXE1413495February2021
508-Nov-20QAXE83817C3November2020
617-Mar-21QAXE83817C20March2021
718-Mar-21QAXE87825A1March2021
805-Feb-21HXE1413492February2021
902-OctAAAAAB9October2021flag
1001-AprAXE877252April2021
1120-AprAXE877251April2021
1217-MayHXE1413491May2021
1322-JunQAXE83817C6June2021
1404-JulQAXE87825A4July2021
1501-AugHXE1413493August2021
1616-AugQAXE83817C2August2021
1708-SepQAXE87825A8September2021
1807-SepHXE1413491September2021
1909-OctAXE877251October2021
2003-NovAXE877253November2021
2130-DecHXE1413492December2021
Details
Cell Formulas
RangeFormula
D2:D21D2=TEXT(A2,"mmmm")
E2:E21E2=TEXT(A2,"yyyy")
F6:F21,F2:F4F2=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

TEST.xlsx
ABCDEF
1Ship DatePart NumberOrder QtyMonthYearNot Present in Buckets
210-Jan-21AXE8772520January2021
311-Jan-21AXE877252January2021
402-Nov-20HXE1413495November2020
522-Feb-21HXE1413491February2021
617-Mar-21QAXE83817C5March2021
718-Mar-21QAXE87825A7March2021
805-Feb-21HXE1413493February2021
930-SepCCCAT9September2021flag
1020-AprAXE877251April2021
1117-MayAXE877252May2021
1222-JunHXE1413492June2021
1310-SepQAXE83817C6September2021
1409-OctQAXE87825A1October2021
1503-NovHXE1413493November2021
1612-DecQAXE83817C2December2021
1708-OctQAXE87825A1October2021
1815-NovHXE1413491November2021
WEB EDI
Cell Formulas
RangeFormula
D2:D18D2=TEXT(A2,"mmmm")
E2:E18E2=TEXT(A2,"yyyy")
F5:F18,F2:F3F2=IF(ISERROR(VLOOKUP(B2,Buckets!A:A,1,FALSE)),"flag","")

#### Fluff

##### MrExcel MVP, Moderator
With the new layout it needs to be
Excel Formula:
``````=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

##### Well-known Member
With the new layout it needs to be
Excel Formula:
``````=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

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
2
Views
51
Replies
3
Views
98
Replies
5
Views
139
Replies
0
Views
66
Replies
2
Views
117