How to write shorter formula for the following average calculation?

[Dezom]

Hello Everyone,
In the following table, What I want is to get the values of "Average of the Duration for Second work and New Work concepts in each week for John". For that I have used two times SUMIFS because of the two different concepts of work and I have used one time SUMPRODUCT formula. How would we write that with shorter? Thank you

Countifs question.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Worker	Week	Concept	Duration	Average Duration
2	John	1.week	Correction	20			Average of the Duration for Second work and New Work concepts in each week for John
3	John	1.week	Second work	20
4	John	1.week	New Work	20			1.week	17,333333
5	Karhan	2.week	MK	20			2.week	#SAYI/0!
6	John	1.week	New Work	12			3.week	#SAYI/0!
7	Kylee	2.week	SK	12			4.week	#SAYI/0!
8	Tomas	6.week	SK	20			5.week	#SAYI/0!
9	John	2.week	SK	12			6.week	15
10	Mattheus	3.week	SK	12			7.week	#SAYI/0!
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Data

Cell Formulas
Range	Formula
H4:H10	H4	=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))

 

[Bo_Ry]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Worker	Week	Concept	Duration	Average Duration
2	John	1.week	Correction	20			Average of the Duration for Second work and New Work concepts in each week for John
3	John	1.week	Second work	20
4	John	1.week	New Work	20			1.week	17.333333	17.333333	17.333333	17.333333
5	Karhan	2.week	MK	20			2.week	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
6	John	1.week	New Work	12			3.week	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
7	Kylee	2.week	SK	12			4.week	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
8	Tomas	6.week	SK	20			5.week	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
9	John	2.week	SK	12			6.week	15	15	15	15
10	Mattheus	3.week	SK	12			7.week	#DIV/0!	#DIV/0!	#DIV/0!	#DIV/0!
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Sheet3

Cell Formulas
Range	Formula
H4:H10	H4	=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))
I4:I10	I4	=AVERAGEIFS($D$2:$D$30,$C$2:$C$30,"*work",$B$2:$B$30,G4,$A$2:$A$30,"John")
J4:J10	J4	=AVERAGE(IF($B$2:$B$30=G4,IF($A$2:$A$30="John",IF(ISNUMBER(MATCH($C$2:$C$30,{"Second work","New Work"},)),$D$2:$D$30))))
K4:K10	K4	=SUMPRODUCT(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUMPRODUCT(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))

 

[Fluff]

How about

=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))

 

[Dezom]

Hello Bo_Ry Thanks a lor.

The last one is not working in my excel. And I do not understand why my excel works with ";" but yours "," I am using pro 2016.
=SUMPRODUCT(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUMPRODUCT(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))

 

[Dezom]

How about

=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))

Hello Fluff thats not working. thats giving me error with that formula. It is totally same as what you have written

 

[Fluff]

That gives me the same answers as your formula

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	Worker	Week	Concept	Duration	Average Duration
2	John	1.week	Correction	20			Average of the Duration for Second work and New Work concepts in each week for John
3	John	1.week	Second work	20
4	John	1.week	New Work	20			1.week	17.3333333	17.3333333
5	Karhan	2.week	MK	20			2.week	#DIV/0!	#DIV/0!
6	John	1.week	New Work	12			3.week	#DIV/0!	#DIV/0!
7	Kylee	2.week	SK	12			4.week	#DIV/0!	#DIV/0!
8	Tomas	6.week	SK	20			5.week	#DIV/0!	#DIV/0!
9	John	2.week	SK	12			6.week	15	15
10	Mattheus	3.week	SK	12			7.week	#DIV/0!	#DIV/0!
11	Dennis	3.week	SK	12
12	Markus	3.week	SK	20
13	Tomas	4.week	New Work	14
14	Karhan	4.week	New Work	2
15	Mattheus	4.week	New Work	4
16	Karhan	4.week	New Work	2
17	Karhan	5.week	Correction	14
18	John	5.week	Correction	5
19	Kylee	5.week	Correction	3
20	Kylee	5.week	Correction	6
21	Kylee	5.week	New Work	2
22	Jonnathan	5.week	New Work	0
23	Daniel	5.week	Second work	22
24	Solomon	6.week	Production Sequence	3
25	John	6.week	Second work	15
26	Mattheus	6.week	New Work	18
27	Kylee	7.week	New Work	10
28	Dennis	7.week	Second work	14
29	Solomon	7.week	New Work	14
30	Jonnathan	7.week	New Work	14
Data

Cell Formulas
Range	Formula
H4:H10	H4	=(SUMIFS($D$2:$D$30,$C$2:$C$30,"Second work",$B$2:$B$30,G4,$A$2:$A$30,"John")+ SUMIFS($D$2:$D$30,$C$2:$C$30,"New Work",$B$2:$B$30,G4,$A$2:$A$30,"John")) /SUMPRODUCT(($B$2:$B$30=G4)*($C$2:$C$30={"Second work","New Work"})*($A$2:$A$30="John"))
I4:I10	I4	=SUM(SUMIFS($D$2:$D$30,$C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))/SUM(COUNTIFS($C$2:$C$30,{"Second work","New Work"},$B$2:$B$30,G4,$A$2:$A$30,"John"))

 

[Bo_Ry]

I do not understand why my excel works with ";" but yours ","

Different regions use different array separators.
For UK, US
Columns ,
Rows ;

For EU
Columns /
Rows ;

Change to / or ; should work.
{"Second work"/"New Work"}

 

[sandy666]

with Power Query

Worker	Week	Concept	Duration		Worker	Week	Avg
John	1.week	Correction	20		John	1.week	17.33333333
John	1.week	Second work	20		John	6.week	15
John	1.week	New Work	20
Karhan	2.week	MK	20
John	1.week	New Work	12
Kylee	2.week	SK	12
Tomas	6.week	SK	20
John	2.week	SK	12
Mattheus	3.week	SK	12
Dennis	3.week	SK	12
Markus	3.week	SK	20
Tomas	4.week	New Work	14
Karhan	4.week	New Work	2
Mattheus	4.week	New Work	4
Karhan	4.week	New Work	2
Karhan	5.week	Correction	14
John	5.week	Correction	5
Kylee	5.week	Correction	3
Kylee	5.week	Correction	6
Kylee	5.week	New Work	2
Jonnathan	5.week	New Work	0
Daniel	5.week	Second work	22
Solomon	6.week	Production Sequence	3
John	6.week	Second work	15
Mattheus	6.week	New Work	18
Kylee	7.week	New Work	10
Dennis	7.week	Second work	14
Solomon	7.week	New Work	14
Jonnathan	7.week	New Work	14

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filter = Table.SelectRows(Source, each ([Worker] = "John") and ([Concept] = "New Work" or [Concept] = "Second work")),
    Group = Table.Group(Filter, {"Worker", "Week"}, {{"Avg", each List.Average([Duration]), type number}})
in
    Group

 

[montecarlo2012]

what about

 

[sandy666]

also you can use "short" Pivot Table

Concept	(Multiple Items)
Average of Duration	Week
Worker	1.week	6.week
John	17.33333333	15