Calculate Weekly movements

[Shp1]

HI Guys,

Quick question.. I am looking to work out the following:

Week 1	#
Number of orders remaining in Shipping Station Queue	How many orders outstanding that week
Number of orders received from warehouse
Number of orders shipped to customers
Number of Faulty items returned to warehouse

The data set is coming from another excel spreadsheet and looks something like this:

Order No	Date order received from warehouse	Date item shipped to customer	Date faulty item Returned to Warehouse	Position
1	06/01/2025			Warehouse
2	06/01/2025			Shipping station
3	07/01/2025	09/01/2025		Courier
4	08/01/2025	07/01/2025		Customer - Complete
5	08/01/2025	17/01/2025	08/01/2025	Returned - Faulty
6	08/01/2025			Warehouse
7	09/01/2025			Shipping station
8	09/01/2025	13/01/2025		Courier
9	10/01/2025	13/01/2025		Customer - Complete
10	13/01/2025	15/01/2025	13/01/2025	Returned - Faulty
11	13/01/2025			Warehouse
12	14/01/2025			Shipping station
13	15/01/2025	17/01/2025		Courier
14	15/01/2025	17/01/2025		Customer - Complete
15	15/01/2025	17/01/2025	15/01/2025	Returned - Faulty
16	16/01/2025			Warehouse
17	16/01/2025			Shipping station
18	17/01/2025	20/01/2025		Courier
19	17/01/2025	21/01/2025		Customer - Complete
20	17/01/2025	21/01/2025	17/01/2025	Returned - Faulty

Any idea on how I could do this?

 

[Shp1]

OR does it include "courier " - so thats another 2

So lets use "Courier" as this status would be used first in the position column before it is changed to "Customer - Complete" for each order.

 

[etaf]

so you want to use both
=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"customer - complete")+COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"courier")

Week 1 #
Number of orders remaining in Shipping Station Queue 2 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")
Number of orders received from warehouse 2 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"warehouse")
Number of orders shipped to customers 4 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"customer - complete")+COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"courier")
Number of Faulty items returned to warehouse 1 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")

Book2
	A	B	C	D	E	F	G	H	I
1	Order No	Date order received from warehouse	Date item shipped to customer	Date faulty item Returned to Warehouse	Position	Week number		Week 1	#
2	1	1/6/25			Warehouse	1		Number of orders remaining in Shipping Station Queue	2
3	2	1/6/25			Shipping station	1		Number of orders received from warehouse	2
4	3	1/7/25	1/9/25		Courier	1		Number of orders shipped to customers	4
5	4	1/8/25	1/7/25		Customer - Complete	1		Number of Faulty items returned to warehouse	1
6	5	1/8/25	1/17/25	1/8/25	Returned - Faulty	1
7	6	1/8/25			Warehouse	1
8	7	1/9/25			Shipping station	1
9	8	1/9/25	1/13/25		Courier	1
10	9	1/10/25	1/13/25		Customer - Complete	1
11	10	1/13/25	1/15/25	1/13/25	Returned - Faulty	2
12	11	1/13/25			Warehouse	2
13	12	1/14/25			Shipping station	2
14	13	1/15/25	1/17/25		Courier	2
15	14	1/15/25	1/17/25		Customer - Complete	2
16	15	1/15/25	1/17/25	1/15/25	Returned - Faulty	2
17	16	1/16/25			Warehouse	2
18	17	1/16/25			Shipping station	2
19	18	1/17/25	1/20/25		Courier	2
20	19	1/17/25	1/21/25		Customer - Complete	2
21	20	1/17/25	1/21/25	1/17/25	Returned - Faulty	2
Sheet1

Cell Formulas
Range	Formula
I2	I2	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")
I3	I3	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"warehouse")
I4	I4	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"customer - complete")+COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"courier")
I5	I5	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")
F2:F21	F2	=IF(B2="","",WEEKNUM(B2,2)-1)