a sumifs statement that is within a date range?

[bigdan]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Order Date[/TD]
[TD]Type[/TD]
[TD]Order Status[/TD]
[TD]Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019-07-01[/TD]
[TD]Bulk[/TD]
[TD]Completed[/TD]
[TD]$5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2019-07-09[/TD]
[TD]Parcel[/TD]
[TD]Shipped[/TD]
[TD]$7[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I need to figure out the weekly shipping costs for Parcel packages. At the moment I'm doing it manually because I can't figure out how to write a sumif statement with a range of dates.

This sheet has about 100K rows, so it's quite extensive. I want to see everything for 7 days, week ending Sun, that is Type = Parcel, Order Status = Completed, and I want the total Cost for that.

Can anyone advise?

Thanks!

 

[Michael M]

Maybe this

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Order Date	Type	Order Status	Cost			start date	end date
2	1/07/2019	Bulk	Completed	$5			27/09/2019	6/11/2019
3	9/07/2019	Parcel	Shipped	$7
4	17/07/2019	Bulk	Completed	$9
5	25/07/2019	Parcel	completed	$11			66
6	2/08/2019	Bulk	Completed	$13
7	10/08/2019	Parcel	Shipped	$15
8	18/08/2019	Bulk	Completed	$17
9	26/08/2019	Parcel	Shipped	$19
10	3/09/2019	Bulk	Completed	$21
11	11/09/2019	Parcel	Shipped	$23
12	19/09/2019	Bulk	Completed	$25
13	27/09/2019	Parcel	Shipped	$27
14	5/10/2019	Bulk	Completed	$29
15	13/10/2019	Parcel	completed	$31
16	21/10/2019	Bulk	Completed	$33
17	29/10/2019	Parcel	completed	$35
18	6/11/2019	Bulk	Completed	$37
19	14/11/2019	Parcel	Shipped	$39
20	22/11/2019	Bulk	Completed	$41
21	30/11/2019	Parcel	Shipped	$43
22	8/12/2019	Bulk	Completed	$45
Sheet1

Cell Formulas
Range	Formula
G5	=SUMPRODUCT(--(C2:C22="Completed")*(B2:B22="Parcel")*(A2:A22>=G2)*(A2:A22<=H2)*D2:D22)

 

[Michael M]

I guess if you wanted to go a step further you could create a couple of dropdowns for Status and type

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J
1	Order Date	Type	Order Status	Cost			start date	end date	Type	Status
2	1/07/2019	Bulk	Completed	$5			25/07/2019	25/07/2019	Parcel	Completed
3	9/07/2019	Parcel	Shipped	$7
4	17/07/2019	Bulk	Completed	$9
5	25/07/2019	Parcel	completed	$11			$ 11.00
6	2/08/2019	Bulk	Completed	$13
7	10/08/2019	Parcel	Shipped	$15
8	18/08/2019	Bulk	Completed	$17
9	26/08/2019	Parcel	Shipped	$19
10	3/09/2019	Bulk	Completed	$21
11	11/09/2019	Parcel	Shipped	$23
12	19/09/2019	Bulk	Completed	$25
13	27/09/2019	Parcel	Shipped	$27
14	5/10/2019	Bulk	Completed	$29
15	13/10/2019	Parcel	completed	$31
16	21/10/2019	Bulk	Completed	$33
17	29/10/2019	Parcel	completed	$35
18	6/11/2019	Bulk	Completed	$37
19	14/11/2019	Parcel	Shipped	$39
20	22/11/2019	Bulk	Completed	$41
21	30/11/2019	Parcel	Shipped	$43
22	8/12/2019	Bulk	Completed	$45
Sheet1

Cell Formulas
Range	Formula
G5	=SUMPRODUCT(--(C2:C22=J2)*(B2:B22=I2)*(A2:A22>=G2)*(A2:A22<=H2)*D2:D22)

 

[bigdan]

Maybe this

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=SUMPRODUCT(--(C2:C22="Completed")*(B2:B22="Parcel")*(A2:A22>=G2)*(A2:A22<=H2)*D2:D22)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Great, thanks! I'm not that all that familiar with sumproduct yet but it seems fairly self-explanatory, at least in this case. Why is there a "--" before?

I guess if you wanted to go a step further you could create a couple of dropdowns for Status and type

I'm sorry I didn't follow?

 

[Michael M]

If you created dropdown lists in Col I and J you would be able to input any type and / or status and return the result !!
As for the "--", see here for further explanations

https://www.mrexcel.com/forum/excel-questions/497594-using-double-unary.html

 

[bigdan]

If you created dropdown lists in Col I and J you would be able to input any type and / or status and return the result !!
As for the "--", see here for further explanations

Ahh!! Didn't think of that, thanks! In your previous message I didn't catch the change in the variable, from writing "Parcel" to I2.

My computer goes by M/D/Y. I created a bunch of dates to test this formula and it works - but is there an easier way to change a range of d/m/y formulas to m/d/y?

 

[Michael M]

One way to try...


1. Select the entire dataset and sort the Date column in ascending order

2. Select the column of dates and go to Data > Text to columns > Next > Next > Date > MDY > Finish

 

[bigdan]

Didn't work. No problem, thanks for trying!

One way to try...


1. Select the entire dataset and sort the Date column in ascending order

2. Select the column of dates and go to Data > Text to columns > Next > Next > Date > MDY > Finish