Question about formulation of some desired output with CountIFS?

[Dezom]

Hi Everyone,
I need to formulate following two desired output whose explanations can be also found in EXCEL I have copied here.
I though I can use only countifs to formulate them but I could not get it worked. I will be glad with your help.

Thanks you in advance

Quextion countifs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Worker	Task	Week	Deadline	Work Statu	Confirmation date of finishing
2	Worker1	A	Week43	21.10.2020	OK	23.10.2020
3	Worker2	B	Week43	23.10.2020	OK	23.10.2020
4	Worker3	C	Week43	21.10.2020	OK	23.10.2020		Number of Delayed work				Today's Date	Last week	Worker of the last Week
5	Worker4	D	Week43	23.10.2020	Processed			Formulation?				25.10.2020	Week43	Formulation?
6	Worker3	E	Week43	22.10.2020	Processed			Explanation two filters are required 1- Work statu = "Processed" 2- Today's date(dynamic date) > Deadline						Explanation Choose the worker whose delayed number of work is minimum while week =Week43 Count the Confirmation date of finishing = Blank and Deadline>Confirmation date of finishing) and choose the worker who has min of this counting. For example for Week43 , Worker1 is the best one
7	Worker6	F	Week44	28.10.2020	Processed
8	Worker6	G	Week41	10.10.2020	OK	10.10.2020
9	Worker8	H	Week43	20.10.2020	Waiting
10	Worker1	K	Week43	22.10.2020	OK	21.10.2020
11	Worker2	M	Week45	2.11.2020	Processed
12
13
14
15
Sheet1

Cell Formulas
Range	Formula
L5	L5	=TODAY()

 

[jasonb75]

For the first part

=COUNTIFS(E:E,"Processed",D:D,"<"&L5)

For the second part, why Worker1? Based on your criteria, Worker1, Worker2, Worker3 and Worker6 are all equal with a count of 0.

 

[Dezom]

For the first part

=COUNTIFS(E:E,"Processed",D:D,"<"&L5)

For the second part, why Worker1? Based on your criteria, Worker1, Worker2, Worker3 and Worker6 are all equal with a count of 0.

Hello Thanks a lot. first one is working as it should be.

For second one You are right. I made a mistake. Please consider this table below.
Here For Week43, Worker1 has the best performance. Because there is not task which he still working on after the deadline or which he finished and confirmed after the deadline as you see.
If there would have been another worker whose tasks are also confirmed until its deadline. Both would be the equal. In this situation Formula should look at task number of the worker. The worker who has more task in this week should be choosen as best performance worker.

Quextion countifs.xlsx
	A	B	C	D	E	F
1	Worker	Task	Week	Deadline	Work Statu	Confirmation date of finishing
2	Worker1	A	Week43	21.10.2020	OK	21.10.2020
3	Worker2	B	Week43	23.10.2020	OK	24.10.2020
4	Worker3	C	Week43	21.10.2020	OK	23.10.2020
5	Worker4	D	Week43	23.10.2020	Processed
6	Worker3	E	Week43	22.10.2020	Processed
7	Worker6	F	Week44	28.10.2020	Processed
8	Worker6	G	Week41	10.10.2020	OK	10.10.2020
9	Worker8	H	Week43	20.10.2020	Waiting
10	Worker1	K	Week43	22.10.2020	OK	21.10.2020
11	Worker2	M	Week45	2.11.2020	Processed
Sheet1

 

[jasonb75]

See if this gives you what you want,

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Worker	Task	Week	Deadline	Work Statu	Confirmation date of finishing	Performance
2	Worker1	A	Week43	21/10/2020	OK	23/10/2020	2
3	Worker2	B	Week43	23/10/2020	OK	23/10/2020	1
4	Worker3	C	Week43	21/10/2020	OK	23/10/2020	1	Number of Delayed work	Today's Date	Last week	Worker of the last Week
5	Worker4	D	Week43	23/10/2020	Processed		0	2	25/10/2020	Week43	Worker1
6	Worker3	E	Week43	22/10/2020	Processed		1
7	Worker6	F	Week44	28/10/2020	Processed		0
8	Worker6	G	Week41	10/10/2020	OK	10/10/2020	0
9	Worker8	H	Week43	20/10/2020	Waiting		0
10	Worker1	K	Week43	22/10/2020	OK	21/10/2020	2
11	Worker2	M	Week45	02/11/2020	Processed		1
Sheet9

Cell Formulas
Range	Formula
H5	H5	=COUNTIFS(E:E,"Processed",D:D,"<"&I5)
I5	I5	=TODAY()
K5	K5	=INDEX($A$2:$A$11,MATCH(MAX($G$2:$G$11),$G$2:$G$11,0))
G2:G11	G2	=COUNTIFS($A$2:$A$11,A2,$C$2:$C$11,$J$5,$F$2:$F$11,"<>")

 

[Dezom]

See if this gives you what you want,

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Worker	Task	Week	Deadline	Work Statu	Confirmation date of finishing	Performance
2	Worker1	A	Week43	21/10/2020	OK	23/10/2020	2
3	Worker2	B	Week43	23/10/2020	OK	23/10/2020	1
4	Worker3	C	Week43	21/10/2020	OK	23/10/2020	1	Number of Delayed work	Today's Date	Last week	Worker of the last Week
5	Worker4	D	Week43	23/10/2020	Processed		0	2	25/10/2020	Week43	Worker1
6	Worker3	E	Week43	22/10/2020	Processed		1
7	Worker6	F	Week44	28/10/2020	Processed		0
8	Worker6	G	Week41	10/10/2020	OK	10/10/2020	0
9	Worker8	H	Week43	20/10/2020	Waiting		0
10	Worker1	K	Week43	22/10/2020	OK	21/10/2020	2
11	Worker2	M	Week45	02/11/2020	Processed		1
Sheet9

Cell Formulas
Range	Formula
H5	H5	=COUNTIFS(E:E,"Processed",D:D,"<"&I5)
I5	I5	=TODAY()
K5	K5	=INDEX($A$2:$A$11,MATCH(MAX($G$2:$G$11),$G$2:$G$11,0))
G2:G11	G2	=COUNTIFS($A$2:$A$11,A2,$C$2:$C$11,$J$5,$F$2:$F$11,"<>")

Hello Thanks
It works in that way.