SUMIF Prior 3 months

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

SOURCE DATA
Part #Date ShippedQTY
112/20/20182,658
112/21/20183,697
112/29/20181,000
21/1/20191,254
21/1/20191,234
21/2/20191,254
31/2/20191,698
31/5/2019542
31/10/2019429
22/10/20194,258
12/10/2019251
12/10/20193,269
12/11/2019258
22/12/2019654
22/12/2019111
32/12/201912,569
12/15/20193,254
22/15/20197,826
32/15/20192,596
42/15/20194,245
13/1/20194,269
23/1/20193,249
13/1/20199,215
23/1/20192,150
33/1/20192,301
43/1/20196,580
33/15/20193,291
33/15/20191,928
23/15/20192,397
23/15/20194,528
24/10/20192,015
14/10/20191,025

<tbody>
</tbody>

DESIRED RESULTS
PartTotal shippedPrior 3 months
128,89620,516
230,93028,915
325,35425,354
410,82510,825

<tbody>
</tbody>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
I'm trying to figure out how to make a SUMIF formula work based on the prior 3 months

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
Hi, here is an alternative option that uses SUMIFS()


Worksheet Formulas
CellFormula
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))

<tbody>
</tbody>

<tbody>
</tbody>

oh man... i was soooo close to this one. Thank you also FormR! you are a rockstar!
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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