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]

When posting data using the XL2BB add-in, you need to select all the relevant cells, not just one.

 

[ODSCm]

When posting data using the XL2BB add-in, you need to select all the relevant cells, not just one.

I just realized the mistake after uploading it but now it should be okey.

 

[Fluff]

Ok, for the last one you can use

=SUMPRODUCT((H2:H30>=DATE(2020,10,21))*(H2:H30<=DATE(2020,10,22)),E2:E30)

For the other 3, leave them as they are.

 

[ODSCm]

Ok, for the last one you can use

=SUMPRODUCT((H2:H30>=DATE(2020,10,21))*(H2:H30<=DATE(2020,10,22)),E2:E30)

For the other 3, leave them as they are.

Thanks Fluff.
For other three isnt there a way to write also with indicated formulas?

 

[Fluff]

For Question
1) You need 3 different arrays, which you cannot do with countifs.
2) Using something like H2:H30>G2:G30 in countifs will treat it as an array, rather than a row by row comparison, so you will end up with an answer of 54.
3) Sumifs requires a range for the sum, & H2:H30-G2:G30 will create an array, so it won't work, although you could try to do two sumifs & subtract one from the other.

 

[Marcelo Branco]

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.

 

[Marcelo Branco]

Test - observe column C

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

Cell Formulas
Range	Formula
L3	L3	=SUM(COUNTIFS(B2:B30,{"John";"Kylee"},D2:D30,{"Correction","New Work"},C2:C30,">=1",C2:C30,"<=4"))
L4	L4	=SUMPRODUCT((B2:B30="John")+(B2:B30="Kylee"),(D2:D30="Correction")+(D2:D30="New Work"),--(C2:C30>=1),--(C2:C30<=4))
L7	L7	=SUMPRODUCT(--(B2:B30="John"),--(H2:H30>G2:G30))
L8	L8	=COUNTIFS(B2:B30,"John",H2:H30,">"&G2:G30)
L11	L11	=SUMPRODUCT(--(B2:B30="John"),--(H2:H30>G2:G30),H2:H30-G2:G30)
I6:I30	I6	=IF(B6="John",IF(H6>G6,H6-G6,""),"")

Ignore formulas in column I (used only for testing purposes)

M.

 

[Fluff]

Hmm, obviously 365 calculates countifs with arrays differently, as I get 54 for L8 rather than 3.

 

[Marcelo Branco]

Hmm, obviously 365 calculates countifs with arrays differently, as I get 54 for L8 rather than 3.

Oh...I'm really surprised. Weird..

M.