COUNTIFS with criteria for status of work item

[surkdidat]

In Sheet 1 I have
Column P = WIP
Column R = Testing
Column Z = Completed

In Sheet 2, from L164 downwards I have dates, 1/4/19,8/4/19, 15/4/19 etc

What I need is to count the number of occurances of each inthat week

So,

Order | W.I.P. | Testing | Completed
100001|1/4/19|10/4/19|14/4/19|
100002|05/04/19|
100003|08/04/19|23/4/19|

So in
01/04/19 week there would be 2 count of WIP, and 0 testing orcompleted
08/04/19 week there would be 2 in WIP, 0 in Testing, 1complete (as 1 in testing was completed same week)
15/04/19 week would be 1 in 2 in WIP
22/04/19 week 1 in WIP, 1 in Testing
(So when completed only counts for that week.

The answers will be in separate cells from M163 (WIP), N163(Testing), 0163 (Completed)

Many thanks

 

[CalcSux78]

Excel 2010

	L	M	N	O
Week Beginning	W.I.P.	Testing	Completed

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]163[/TD]

[TD="align: center"]164[/TD]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]165[/TD]
[TD="align: right"]08/04/2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]166[/TD]
[TD="align: right"]15/04/2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]167[/TD]
[TD="align: right"]22/04/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]168[/TD]
[TD="align: right"]29/04/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet2​

[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] "]M164[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!P:P,"<"&$L164+7,Sheet1!R:R,">"&$L164+7),COUNTIFS(Sheet1!P:P,"<"&$L164+7,Sheet1!R:R,""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]N164[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!R:R,"<"&$L164+7,Sheet1!Z:Z,">"&$L164+7),COUNTIFS(Sheet1!R:R,"<"&$L164+7,Sheet1!Z:Z,""))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O164[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!Z:Z,"<"&$L164+7,Sheet1!Z:Z,">="&$L164))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[surkdidat]

Thank you for this. This does 99% of what I need.

The only scenario I have come across is if I was in WIP or Testing on 01/04/19 and Completed on 05/04/19, it should then not record it in WIP (only latest status that week (Also amended from 7 to 6 as for week, as it seemed to be counting Mon-Mon rather than Mon-Sun)

Excel 2010

	L	M	N	O
Week Beginning	W.I.P.	Testing	Completed

<tbody>
[TD="align: center"]163
[/TD]

[TD="align: center"]164
[/TD]
[TD="align: right"]01/04/2019
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]165
[/TD]
[TD="align: right"]08/04/2019
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]166
[/TD]
[TD="align: right"]15/04/2019
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]167
[/TD]
[TD="align: right"]22/04/2019
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]168
[/TD]
[TD="align: right"]29/04/2019
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH]M164
[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!P:P,"<"&$L164+7,Sheet1!R:R,">"&$L164+7),COUNTIFS(Sheet1!P:P,"<"&$L164+7,Sheet1!R:R,""))
[/TD]
[/TR]
[TR]
[TH]N164
[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!R:R,"<"&$L164+7,Sheet1!Z:Z,">"&$L164+7),COUNTIFS(Sheet1!R:R,"<"&$L164+7,Sheet1!Z:Z,""))
[/TD]
[/TR]
[TR]
[TH]O164
[/TH]
[TD="align: left"]=SUM(COUNTIFS(Sheet1!Z:Z,"<"&$L164+7,Sheet1!Z:Z,">="&$L164))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]