Help with SUMIFS with multiple conditions

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
658
Office Version
  1. 2019
Platform
  1. Windows
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:

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","")
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Jul 24, 2019
Messages
658
Office Version
  1. 2019
Platform
  1. Windows
How about
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
Joined
Jul 24, 2019
Messages
658
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 24, 2019
Messages
658
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.

1604587075517.png


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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARAS
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))
AD7:AD11AD7=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: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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
658
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
51,128
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,228
Messages
5,576,858
Members
412,750
Latest member
sammurraybooks
Top