SUMIF Prior 3 months

Geo1126

Geo1126

New Member
Joined
Apr 15, 2019
Messages
9
Greetings Excel Wizards.

I'm trying to figure out how to make a SUMIF formula work based on the prior 3 months of the current month (excluding the current month). I have hundreds of parts with hundreds of shipments on my source data sheet (a shipping log). on a separate table I have a single line per part that totals my various data. I want to be add a data collect for those prior 3 months and have been stumped on how to do it.

I'm maybe a set up from complete novice with excel, and everything I've learned thus far has been self taught. Can't get this one going. I apologize if this has already been covered, i did spend a little time searching for the answer.

here is a very basic example. This assumes the current date is in April. Thus it excludes the April and December dates from desired results. Only want Prior 3 months, Jan/Feb/Mar. I hope this makes sense, and I truly appreciate any help on this. lets say Part Number is column A, Date is B and QTY is C

[TABLE="width: 779"]
<tbody>[TR]
[TD][/TD]
[TD]SOURCE DATA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Part #[/TD]
[TD]Date Shipped[/TD]
[TD]QTY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/20/2018[/TD]
[TD]2,658[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/21/2018[/TD]
[TD]3,697[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12/29/2018[/TD]
[TD]1,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]1,254[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/1/2019[/TD]
[TD]1,234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1/2/2019[/TD]
[TD]1,254[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/2/2019[/TD]
[TD]1,698[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/5/2019[/TD]
[TD]542[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1/10/2019[/TD]
[TD]429[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/10/2019[/TD]
[TD]4,258[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/10/2019[/TD]
[TD]251[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/10/2019[/TD]
[TD]3,269[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/11/2019[/TD]
[TD]258[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/12/2019[/TD]
[TD]654[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/12/2019[/TD]
[TD]111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/12/2019[/TD]
[TD]12,569[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2/15/2019[/TD]
[TD]3,254[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2/15/2019[/TD]
[TD]7,826[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2/15/2019[/TD]
[TD]2,596[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2/15/2019[/TD]
[TD]4,245[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/1/2019[/TD]
[TD]4,269[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/1/2019[/TD]
[TD]3,249[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3/1/2019[/TD]
[TD]9,215[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/1/2019[/TD]
[TD]2,150[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/1/2019[/TD]
[TD]2,301[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/1/2019[/TD]
[TD]6,580[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/15/2019[/TD]
[TD]3,291[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/15/2019[/TD]
[TD]1,928[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/15/2019[/TD]
[TD]2,397[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/15/2019[/TD]
[TD]4,528[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4/10/2019[/TD]
[TD]2,015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4/10/2019[/TD]
[TD]1,025[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

DESIRED RESULTS
[TABLE="width: 500"]
<tbody>[TR]
[TD]Part[/TD]
[TD]Total shipped[/TD]
[TD]Prior 3 months[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]28,896[/TD]
[TD]20,516[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30,930[/TD]
[TD]28,915[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]25,354[/TD]
[TD]25,354[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10,825[/TD]
[TD]10,825[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
How about


Excel 2013/2016
ABCDEFGH
4Part #Date ShippedQTYPartTotal shippedPrior 3 months
5129/12/20181,000128,89620516
6201/01/20191,254230,93028915
7201/01/20191,234325,35425354
8202/01/20191,254410,82510825
9302/01/20191,698
10305/01/2019542
11310/01/2019429
12210/02/20194,258
13110/02/2019251
14110/02/20193,269
15111/02/2019258
16212/02/2019654
17212/02/2019111
18312/02/201912,569
19115/02/20193,254
20215/02/20197,826
List
Cell Formulas
RangeFormula
H5=SUMPRODUCT((MONTH($B$5:$B$34)>=MONTH(TODAY())-3)*(MONTH($B$5:$B$34)TODAY()))*($A$5:$A$34=F5),$C$5:$C$34)
 
Last edited:
Upvote 0
Geo1126 said:
I'm trying to figure out how to make a SUMIF formula work based on the prior 3 months
Click to expand...

Hi, here is an alternative option that uses SUMIFS()


Excel 2013/2016
ABCDEFG
1Part #Date ShippedQTYPartTotal shippedPrior 3 months
2112/20/20182,658128,89620516
3112/21/20183,697230,93028915
4112/29/20181,000325,35425354
5201/01/20191,254410,82510825
6201/01/20191,234
7201/02/20191,254
8301/02/20191,698
9301/05/2019542
10301/10/2019429
11202/10/20194,258
12102/10/2019251
13102/10/20193,269
14102/11/2019258
15202/12/2019654
16202/12/2019111
17302/12/201912,569
18102/15/20193,254
19202/15/20197,826
20302/15/20192,596
21402/15/20194,245
22103/01/20194,269
23203/01/20193,249
24103/01/20199,215
25203/01/20192,150
26303/01/20192,301
27403/01/20196,580
28303/15/20193,291
29303/15/20191,928
30203/15/20192,397
31203/15/20194,528
32204/10/20192,015
33104/10/20191,025
Sheet1
Cell Formulas
RangeFormula
F2=SUMIFS(C:C,A:A,E2)
G2=SUMIFS(C:C,A:A,E2,B:B,">="&EOMONTH(TODAY(),-4)+1,B:B,"<="&EOMONTH(TODAY(),-1))
 
Upvote 0
FormR said:
Hi, here is an alternative option that uses SUMIFS()


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,E2)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=SUMIFS(C:C,A:A,E2,B:B,">="&EOMONTH(TODAY(),-4)+1,B:B,"<="&EOMONTH(TODAY(),-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

oh man... i was soooo close to this one. Thank you also FormR! you are a rockstar!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
675
Domenic
D
A
  • Question Question
Loan Tracker with prior/missed payments
Replies
0
Views
112
aurelius33
A
U
  • Solved
Formula to identify gaps
Replies
5
Views
664
Fluff
Fluff
C
  • Solved
Return a Prior Month's Value from within the Same Table?
2 3
Replies
22
Views
469
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top