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"))

 

[Dezom]

also you can use "short" Pivot Table

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

View attachment 21843

Thanks that is also a way

 

[arthurbr]

Referrring to post #7( I don't know how to use the " quote" feature), the European separator for arrays is the backslash , not the slash

constant array separator in excel formula

What is the seperator list or option to use in constant array to use in a formula excel cell ? all the Sample ex: Guidelines in array formula and help on this show ; as vertical separator and , as

stackoverflow.com

{"Second work"\"New Work"}

 

[Bo_Ry]

@arthurbr
Thanks for the correction.

 

[arthurbr]

Most welcome

 

[sandy666]

Thanks that is also a way

You are welcome