Count active criteria over a period of time

[mrlex]

I have a set of historic order data going back for several years. Simply structured like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Order number[/TD]
[TD]Customer[/TD]
[TD]Order Date[/TD]
[TD]Delivery Date[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]Customer A[/TD]
[TD]20/12/2017[/TD]
[TD]06/01/2018[/TD]
[/TR]
[TR]
[TD]54321[/TD]
[TD]Customer B[/TD]
[TD]01/01/2018[/TD]
[TD]10/01/2018[/TD]
[/TR]
</tbody>[/TABLE]

What I would like is to show is how many active (aka undelivered) orders a customer had on any given date. I would like to represent that as a trend over time. This is for a large range of customers, over a high number of orders, for a long time period.

So on 01/01/2018, how many active (undelivered) orders did Customer A have.
Then on 02/02/2018, how many active (undelivered) orders did Customer A have.

I am really scratching my head to figure out the best way to show this for a range of customers, over a long time period.

Can any one offer suggestions on this one?

Thanks in advance

 

[Scott T]

Is this what you want?

Book1
	A	B	C	D
1	Order number	Customer	Order Date	Delivery Date
2	12345	Customer A	12/20/2017	1/6/2018
3	54321	Customer B	1/1/2018	1/10/2018
4	12456	Customer A	2/2/2018	12/7/2018
5	36173	Customer A	12/17/2017	1/31/2018
6
7
8	Customer A	1/1/2018	2
9	Customer A	2/2/2018	1
Sheet1

Cell Formulas
Range	Formula
C8	=COUNTIFS($B$2:$B$6,A8,$C$2:$C$6,"<="&B8,$D$2:$D$6,">="&B8)
C9	=COUNTIFS($B$2:$B$6,A9,$C$2:$C$6,"<="&B9,$D$2:$D$6,">="&B9)

 

[mrlex]

Thanks so much for this! I am going to have a play around and see if I can get this to work.

I guess the only issue I have is that this will require a very long additional set of data.

If I have around 50 customers, and I want to do this over a period of a year or more, I would need to create a table with 50x360=18,000 rows/calculations. And this would continue to grow as I add more customers, and measure over a longer period - and this would need manual maintenance and updating.

Perhaps this is the only way to do it (and it does work), I just wondered if there was a more elegant solution (perhaps using a calculated field or something). I can't really think of anything though.