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]

It counts every date in col G (regardless of whether it's a "John" row) that is less than a date in col H & B is John

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1	Task No	Worker	Week	Concept	Duration	Status	Deadline	Confirmed Finish Date
2	5532G21	John	1.week	Correction	20	OK	21/10/2020	22/10/2020	3
3	902321R	John	1.week	Second work	20	OK	23/10/2020	24/10/2020	1
4	5536G21	John	1.week	New Work	20	OK	21/10/2020	23/10/2020	3
5	5532G22	Karhan	2.week	MK	20	Processed	23/10/2020	23/10/2020	1
6	5432F21	John	1.week	New Work	12	Processed	22/10/2020		2
7	548222D	Kylee	2.week	SK	12	Processed	28/10/2020		0
8	5593AB1	Tomas	6.week	SK	20	OK	10/10/2020	10/10/2020	3
9	908751Z	John	2.week	SK	12	Waiting	20/10/2020		3
10	5532ABC	Mattheus	3.week	SK	12	OK	22/10/2020	21/10/2020	2
11	5532D09	Dennis	3.week	SK	12	Processed	02/11/2020	03/11/2020	0
12	90RFG67	Markus	3.week	SK	20	OK	21/10/2020	21/10/2020	3
13	54839GE	Tomas	4.week	New Work	14	OK	23/10/2020	21/10/2020	1
14	5532G21	Karhan	4.week	New Work	2	OK	21/10/2020	21/10/2020	3
15	902321R	Mattheus	4.week	New Work	4	Processed	23/10/2020	24/10/2020	1
16	5536G21	Karhan	4.week	New Work	2	Processed	22/10/2020	23/10/2020	2
17	5532G22	Karhan	5.week	Correction	14	Processed	28/10/2020		0
18	9032F21	John	5.week	Correction	5	OK	10/10/2020		3
19	548222D	Kylee	5.week	Correction	3	Waiting	20/10/2020		3
20	5593AB1	Kylee	5.week	Correction	6	OK	22/10/2020	21/10/2020	2
21	908751Z	Kylee	5.week	New Work	2	Processed	02/11/2020	03/11/2020	0
22	5532ABC	Jonnathan	5.week	New Work	0	OK	21/10/2020	23/10/2020	3
23	908751Z	Daniel	5.week	Second work	22	OK	23/10/2020		1
24	5532ABC	John	4.week	Correction	3	OK	21/10/2020		3
25	5532D09	John	6.week	Second work	15	Processed	23/10/2020		1
26	90RFG67	Mattheus	6.week	New Work	18	Processed	22/10/2020	10/10/2020	2
27	54839GE	Kylee	7.week	New Work	10	Processed	28/10/2020		0
28	90RFG67	Dennis	7.week	Second work	14	OK	10/10/2020	21/10/2020	3
29	54839GE	Solomon	7.week	New Work	14	Waiting	20/10/2020		3
30	54839GE	Jonnathan	7.week	New Work	14	OK	22/10/2020		2
Lists

Cell Formulas
Range	Formula
I2:I30	I2	=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
Dynamic array formulas.

 

[Marcelo Branco]

Fluff
The result in I2 seems perfect to me
=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
3

I'm not understanding your problem

M.

 

[ODSCm]

Maybe...

EDIT: i adjusted the column C to show only numbers ( i substituted .week by """)
Using COUNTIFS
Question 1
=SUM(COUNTIFS(B2:B30,{"John";"Kylee"},D2:D30,{"Correction","New Work"},C2:C30,">=1",C2:C30,"<=4"))

Question 2
=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)

M.

Hello Marcelo,
Question 2 with your formula I am getting the result as "0" instead of 3.
Question 1 seems it is working

 

[ODSCm]

Hello Marcelo,
Question 2 with your formula I am getting the result as "0" instead of 3.
Question 1 seems it is working

İt is now working I got result 3 when I made your formula as array (CTRL+shift enter). For all answers I thank Fluff and Marcelo.

 

[Fluff]

@ODSCm are you trying this in xl2016 or a newer version.

The result in I2 seems perfect to me

It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.

 

[Marcelo Branco]

Question 2 with your formula I am getting the result as "0" instead of 3.

It worked for me. See my post above.

M.

 

[Marcelo Branco]

İt is now working I got result 3 when I made your formula as array (CTRL+shift enter).

It worked for me with just Enter
Excel 2010

M.

 

[Marcelo Branco]

It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.

I'm confused...

Why did you wrap it with SUM? AFAIK the formula doesn't require SUM

M.

 

[ODSCm]

@ODSCm are you trying this in xl2016 or a newer version.


It is, but col I is just a single formula, so if I wrap that in SUM it will add all the values together.

My version is MS Office professional plus 2016

 

[Marcelo Branco]

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

M.