Calculate Weekly movements

Shp1 · 2025-01-21T07:21:07-0500

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?

etaf · 2025-01-21T07:26:50-0500

how do you define week1 ???

countifs()
count the number of dates between your week in column Date item shipped to customer, Date order received from warehouse in your range
count the number of dates between your week in column Date faulty item Returned to Warehouse, Date order received from warehouse in your range
count the number in Position with shipping station and blank dates in other columns and Date order received from warehouse in your range

Shp1 · 2025-01-21T07:45:43-0500

So week 1 would be 6-10 Jan 2025 and week 2 would be 13-17 Jan 2025 and so on and so forth

etaf · 2025-01-21T08:10:32-0500

probably ned a table of dates for week 1
OR rather thand week1 use dates
or poss =WEEKNUM(b2,2)-1 in a helper column or mybe a filter()

with a helper
BUT need a definition for some of the criteria

Number of orders remaining in Shipping Station Queue
=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")

Number of orders received from warehouse - how is that defined ??

again
Number of orders shipped to customers
is that
Courier
Customer - Complete
does that also include those returned

WHAT ARE your expected results and WHY

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

2

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

6-10 Jan 2025

10

9

1/10/25

1/13/25

Customer - Complete

1

week 1

#VALUE!

11

10

1/13/25

1/15/25

1/13/25

Returned - Faulty

2

Tue 07/01/2025

2

12

11

1/13/25

Warehouse

2

Wed 08/01/2025

2

13

12

1/14/25

Shipping station

2

Thu 09/01/2025

2

14

13

1/15/25

1/17/25

Courier

2

Fri 10/01/2025

2

15

14

1/15/25

1/17/25

Customer - Complete

2

Sat 11/01/2025

2

16

15

1/15/25

1/17/25

1/15/25

Returned - Faulty

2

Sun 12/01/2025

2

17

16

1/16/25

Warehouse

2

Mon 13/01/2025

3

18

17

1/16/25

Shipping station

2

Tue 14/01/2025

3

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
H2	H2	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")
H3	H3	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"warehouse")
H4	H4	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"customer - complete")
H5	H5	=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")
I10:I16	I10	=ISOWEEKNUM(G10)
H10:H18	H10	=WEEKNUM(G10,2)
F2:F21	F2	=IF(B2="","",WEEKNUM(B2,2)-1)

Shp1 · 2025-01-21T08:51:06-0500

Thanks - Basically I need to track how many orders are being received from our warehouse and what the output is from the shipping team. There is also the added complexity that items are checked before shipping and if they are faulty then these are sent back to the warehouse for a replacement. I also wanted to know how many orders we've got left to process from the weeks before. Trying to work out how much is being processed in each week. Hope this helps explain the exercise.

etaf · 2025-01-21T09:05:25-0500

sorry not really - i understand what you are trying to do - BUT its more about the interpretation of the data you provided
based on the info you have given
what are the expected results and WHY ???
then we can work out the countifs() or maybe some other function

forexample

I need to track how many orders are being received from our warehouse

How many is that based on the data you provided ?

There is also the added complexity that items are checked before shipping and if they are faulty then these are sent back to the warehouse for a replacement

so do we count the number with a items with a "position" of warehouse and also add the returned - Faulty
as a total count
OR what

Shp1 · 2025-01-21T10:18:47-0500

etaf said:
How many is that based on the data you provided ?

Yes

Based on the data I provided the answers should be:

Week 1	#
Number of orders remaining in Shipping Station Queue	1 (Counting orders which state "Shipping Station" in the position column within the selected date range)
Number of orders received from warehouse	1 (counting number of orders received within the selected date range)
Number of orders shipped to customers	1 (Counting number of orders shipped within the selected date range)
Number of Faulty items returned to warehouse	1 (Counting order which state "Faulty - returned" in the position column within the selected date range)

etaf · 2025-01-21T10:38:24-0500

thanks for that
see I previously posted the counts based on position
H2 H2 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")
H3 H3 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"warehouse")
H4 H4 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"customer - complete")
H5 H5 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")
However ,
you say week 1 ,

week 1 would be 6-10 Jan 2025

so 1st result

Number of orders remaining in Shipping Station Queue

1 (Counting orders which state "Shipping Station" in the position column within the selected date range)

is 1 - i count 2
so why do you have 1

Book2

A

B

C

D

E

F

1

Order No

Date order received from warehouse

Date item shipped to customer

Date faulty item Returned to Warehouse

Position

Week number

2

1

1/6/25

Warehouse

1

3

2

1/6/25

Shipping station

1

4

3

1/7/25

1/9/25

Courier

1

5

4

1/8/25

1/7/25

Customer - Complete

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
F2:F21	F2	=IF(B2="","",WEEKNUM(B2,2)-1)

Number of orders received from warehouse

1 (counting number of orders received within the selected date range)

why 1 ? , again i have 2 with Warehouse in position

Book2

A

B

C

D

E

F

1

Order No

Date order received from warehouse

Date item shipped to customer

Date faulty item Returned to Warehouse

Position

Week number

2

1

1/6/25

Warehouse

1

3

2

1/6/25

Shipping station

1

4

3

1/7/25

1/9/25

Courier

1

5

4

1/8/25

1/7/25

Customer - Complete

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
F2:F21	F2	=IF(B2="","",WEEKNUM(B2,2)-1)

Number of orders shipped to customers

1 (Counting number of orders shipped within the selected date range)

What is the criteria for this

and finally

Number of Faulty items returned to warehouse

1 (Counting order which state "Faulty - returned" in the position column within the selected date range)

I count 1 using
=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")

as mentioned and shown in previous posts

Shp1 · 2025-01-21T10:57:08-0500

etaf said:
so why do you have 1

Sorry, you're right it is 2!

etaf said:
why 1 ? , again i have 2 with Warehouse in position

Again, apologies it is 2..

etaf said:
What is the criteria for this

The date that the item was shipped to the customer. Looking for a value based on the number of times a particular date was entered within the date range.

etaf said:
I count 1 using
=COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")

Understood.. thanks!

I think I need a coffee haha!

etaf · 2025-01-21T11:04:38-0500

ok,
so you now have 3 countifs() which work as you have described and posted in my post number 4 - all based on a helper column for weeknumber and the entry in the position column
H2 H2 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"shipping station")
H3 H3 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"warehouse")

H5 H5 =COUNTIFS($F$2:$F$21,1,$E$2:$E$21,"returned - faulty")

so now for

The date that the item was shipped to the customer. Looking for a value based on the number of times a particular date was entered within the date range.

Dont see what criteria to use here
How defined Shipped to customer ?
Based on the entries in Postion column
is it ONLY
"Customer - Complete" and so the result is 2
OR does it include "courier " - so thats another 2

so what do you want to count in "postion" column for this count ?

Calculate Weekly movements

Shp1

New Member

Excel Facts

etaf

Well-known Member

Shp1

New Member

etaf

Well-known Member

Shp1

New Member

etaf

Well-known Member

Shp1

New Member

etaf

Well-known Member

Shp1

New Member

etaf

Well-known Member

Similar threads

Forum statistics

Share this page

Calculate Weekly movements

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock