Count how many "Open" work requests in a particular (historic) week

[surkdidat]

I am hoping this is really simple, and my brain is just not working!

In Column G is the date the request received
In Column H is the "Work Resource"
In Column J is the "Status" - unfortunately, there are several "Open" status'" (and also "Rejected")
In Column Q is the "Completed" date

My week starts on a Sunday. (For instance w/c Sunday 27th February 2022 to Saturday 5th March 2022 or Sunday 6th March 2022 - Saturday 12th March 2022)

What I need to do is have a count of the number of "Open/active" work items in a particular week. The count needs to be whatever is there on the Saturday.

There is also a Column M which has an automated timestamp. So, for instance if Column J - Status is amended to "Rejected" it will input the below. It is worth mentioning that no date is input anywhere else for the date a work item is rejected.

	Column A (Ref Number)	Column G (Received Date)	Column H (Resource)	Column J (Status)	Column Q (Comp. Date)	Column M (Audit Trail)
	10001	18/02/2022	Department A	Complete	28/02/2022
	10002	23/02/2022	Department A	Submitted
	10003	23/02/2022	Department A	In QA
	10004	25/02/2002	Department A	Complete	04/03/2022
	10005	25/02/2022	Department B	Complete	27/02/2022
	10006	25/02/2022	Department B	In investigation
	10007	25/02/2022	Department B	Rejected		28/02/2022 11:15:13 - Status change: Rejected
	10008	01/03/2022	Department A	Submitted
	10009	01/03/2022	Department A	Complete	02/03/2022

So, in essence I need a count of

Open work items
w/c 20/02/2022 (to Saturday 26/2/2022 (ANSWER : 6) ....... 10001 / 10002 / 10003 / 10004 / 10006 / 10007
w/c 27/02/2022 (to Saturday 05/03/2022 (ANSWER : 5) ....... 10002 / 10003 / 10006 / 10007 / 100008

I hope that makes sense and someone can help!

 

[jtakw]

Hi,

Your written description does not match your expected results.
You say you want "a count of the number of "Open/active" work items in a particular week"
But looking at your expected results, it's count of Open/active items up to the end of the particular week, Not within that week.

Based on your expected results, use either formula:

Book3.xlsx
	A						G	H		J			M				Q	R	S	T
1	Column A (Ref Number)						Column G (Received Date)	Column H (Resource)		Column J (Status)			Column M (Audit Trail)				Column Q (Comp. Date)	As of w/c	Open items
2	10001						2/18/2022	Department A		Complete							2/28/2022	2/26/2022	7	7
3	10002						2/23/2022	Department A		Submitted								3/5/2022	5	5
4	10003						2/23/2022	Department A		In QA
5	10004						2/25/2022	Department A		Complete							3/4/2022
6	10005						2/25/2022	Department B		Complete							2/27/2022
7	10006						2/25/2022	Department B		In investigation
8	10007						2/25/2022	Department B		Rejected			28/02/2022 11:15:13 - Status change: Rejected
9	10008						3/1/2022	Department A		Submitted
10	10009						3/1/2022	Department A		Complete							3/2/2022
Sheet1032

Cell Formulas
Range	Formula
S2:S3	S2	=COUNTIFS(G$2:G$10,"<="&R2,Q$2:Q$10,">="&R2)+COUNTIFS(G$2:G$10,"<="&R2,Q$2:Q$10,"")
T2:T3	T2	=SUMPRODUCT((G$2:G$10<=R2)*((Q$2:Q$10>=R2)+(Q$2:Q$10="")))

 

[surkdidat]

Sincere apologies, for not coming back to you. This has worked perfectly. I really appreciate your help!

 

[jtakw]

You're welcome, thanks for the feedback.