Creating formulas with countifs/sumifs/sumproduct

[ODSCm]

Hi All,

I would like to write sum formulas with Countifs, Sumifs and with Sumproduct.
I have added excel file here. I have written what I would like to calculate and I have written equal formula for each question.
I have indicated the formula with red color which I am asking you how to write. Thans for your help.

Excel Question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Task No	Worker	Week	Concept	Duration	Status	Deadline	Confirmed Finish Date
2	5532G21	John	1.week	Correction	20	OK	21.10.20	22.10.20			Question 1-) How can I calculate the number of the tasks whose types are "Correction" and "New Work", between first and fourth week which were worked by John and Kylee
3	902321R	John	1.week	Second work	20	OK	23.10.20	24.10.20			Formula 1	Countifs?
4	5536G21	John	1.week	New Work	20	OK	21.10.20	23.10.20			Formula 2	4	SUMPRODUCT(((D2:D30="Correction")+(D2:D30="New Work"))*($C$2:$C$30={"1.week";"2.week";"3.week";"4.week"})*((B2:B30="John")+(B2:B30="Kylee")))
5	5532G22	Karhan	2.week	MK	20	Processed	23.10.20	23.10.20
6	5432F21	John	1.week	New Work	12	Processed	22.10.20				Quetion 2-) Find the number of delayed tasks depend on selected worker (John)
7	548222D	Kylee	2.week	SK	12	Processed	28.10.20				Formula 1	3	SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John"))
8	5593AB1	Tomas	6.week	SK	20	OK	10.10.20	10.10.20			Formula 2	Countifs?
9	908751Z	John	2.week	SK	12	Waiting	20.10.20
10	5532ABC	Mattheus	3.week	SK	12	OK	22.10.20	21.10.20			Question 3-) Find the duration of delay for delayed tasks of John.
11	5532D09	Dennis	3.week	SK	12	Processed	02.11.20	03.11.20			Formula 1	4	SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>""))
12	90RFG67	Markus	3.week	SK	20	OK	21.10.20	21.10.20			Formula 2	Sumifs?
13	54839GE	Tomas	4.week	New Work	14	OK	23.10.20	21.10.20
14	5532G21	Karhan	4.week	New Work	2	OK	21.10.20	21.10.20			Question 4-) Sum the duration of tasks between specific two dates
15	902321R	Mattheus	4.week	New Work	4	Processed	23.10.20	24.10.20			Formula 1	88	SUMIFS(E:E;H:H;">="&TARİH(2020;10;21);H:H;"<="&TARİH(2020;10;22))
16	5536G21	Karhan	4.week	New Work	2	Processed	22.10.20	23.10.20			Formula 2	88	SUMIFS(E:E;H:H;">=21.10.2020";H:H;"<22.10.2020")
17	5532G22	Karhan	5.week	Correction	14	Processed	28.10.20				Formula 3	SUMPRODUCT?
18	9032F21	John	5.week	Correction	5	OK	10.10.20
19	548222D	Kylee	5.week	Correction	3	Waiting	20.10.20
20	5593AB1	Kylee	5.week	Correction	6	OK	22.10.20	21.10.20
21	908751Z	Kylee	5.week	New Work	2	Processed	02.11.20	03.11.20
22	5532ABC	Jonnathan	5.week	New Work	0	OK	21.10.20	23.10.20
23	908751Z	Daniel	5.week	Second work	22	OK	23.10.20
24	5532ABC	John	4.week	Correction	3	OK	21.10.20
25	5532D09	John	6.week	Second work	15	Processed	23.10.20
26	90RFG67	Mattheus	6.week	New Work	18	Processed	22.10.20	10.10.20
27	54839GE	Kylee	7.week	New Work	10	Processed	28.10.20
28	90RFG67	Dennis	7.week	Second work	14	OK	10.10.20	21.10.20
29	54839GE	Solomon	7.week	New Work	14	Waiting	20.10.20
30	54839GE	Jonnathan	7.week	New Work	14	OK	22.10.20
31
Sheet1

Cell Formulas
Range	Formula
L4	L4	=SUMPRODUCT(((D2:D30="Correction")+(D2:D30="New Work"))*($C$2:$C$30={"1.week","2.week","3.week","4.week"})*((B2:B30="John")+(B2:B30="Kylee")))
L7	L7	=SUMPRODUCT((H2:H30>G2:G30)*(B2:B30="John"))
L11	L11	=SUMPRODUCT((H2:H30-G2:G30)*(B2:B30="John")*(H2:H30<>""))
L15	L15	=SUMIFS(E:E,H:H,">="&DATE(2020,10,21),H:H,"<="&DATE(2020,10,22))
L16	L16	=SUMIFS(E:E,H:H,">=21.10.2020",H:H,"<=22.10.2020")

Thanks for your help

 

[Fluff]

I'm confused...

All the values are returned by a single formula in one cell, it's not a formula that has been dragged down.

 

[ODSCm]

Sorry...i'm wrong
By chance my formula generated the correct result
Shame

M.

Yes I also now realized. With other workers I tested. It gives wrong value. I think there is no way to write it with countif

 

[Marcelo Branco]

All the values are returned by a single formula in one cell, it's not a formula that has been dragged down.

You're right. I didn't test the formula properly

M.

 

[Marcelo Branco]

I think there is no way to write it with countif

Yes, it doesn't seem possible. I tried different formulas, for no avail.

M.