Averageifs

[qarat]

Hi everyone,

I have the following table

[TABLE="width: 425"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Office[/TD]
[TD="align: center"]Position[/TD]
[TD="align: center"]Start Date[/TD]
[TD="align: center"]Termination Date[/TD]
[/TR]
[TR]
[TD="align: center"]New York[/TD]
[TD="align: center"]Manger[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]11-Nov-17[/TD]
[/TR]
[TR]
[TD="align: center"]Chicago[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]28-Dec-18[/TD]
[/TR]
[TR]
[TD="align: center"]Miami[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]13-Feb-17[/TD]
[/TR]
[TR]
[TD="align: center"]Boston[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]2-Apr-17[/TD]
[/TR]
[TR]
[TD="align: center"]Toronto[/TD]
[TD="align: center"]Manger[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]20-May-19[/TD]
[/TR]
[TR]
[TD="align: center"]Seattle[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]8-Jul-17[/TD]
[/TR]
[TR]
[TD="align: center"]Paris[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]26-Aug-19[/TD]
[/TR]
[TR]
[TD="align: center"]London[/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]15-Oct-17[/TD]
[/TR]
[TR]
[TD="align: center"]Berlin[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]20-Jan-16[/TD]
[TD="align: center"]30-Sep-19[/TD]
[/TR]
[TR]
[TD="align: center"]Moscow[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]10-Sep-17[/TD]
[/TR]
[TR]
[TD="align: center"]Tokio[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]11-Aug-19[/TD]
[/TR]
[TR]
[TD="align: center"]Rome[/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]14-May-17[/TD]
[/TR]
[TR]
[TD="align: center"]New York[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]6-Jul-19[/TD]
[/TR]
[TR]
[TD="align: center"]Chicago[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]28-Aug-17[/TD]
[/TR]
[TR]
[TD="align: center"]New York[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]21-Oct-19[/TD]
[/TR]
[TR]
[TD="align: center"]Boston[/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]14-Dec-17[/TD]
[/TR]
[TR]
[TD="align: center"]Toronto[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]7-Feb-27[/TD]
[/TR]
[TR]
[TD="align: center"]Berlin[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]3-Apr-20[/TD]
[/TR]
[TR]
[TD="align: center"]Berlin[/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]29-May-17[/TD]
[/TR]
[TR]
[TD="align: center"]Chicago[/TD]
[TD="align: center"]Director[/TD]
[TD="align: center"]18-Jan-16[/TD]
[TD="align: center"]25-Jul-17[/TD]
[/TR]
</tbody>[/TABLE]



I urgently need to find average number of days all employees worked in the company by office and position using one formula and fill in the following table:

[TABLE="width: 396"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"]Office[/TD]
[TD="align: center"]Manager[/TD]
[TD="align: center"]Senior Manager[/TD]
[TD="align: center"]Director[/TD]
[/TR]
[TR]
[TD="align: center"]New York[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Boston[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Berlin[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
[TR]
[TD="align: center"]Chicago
...[/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[TD="align: center"] [/TD]
[/TR]
</tbody>[/TABLE]

I think that AVERAGEIFS should be used but have no idea how to do this. Also some termination dates are later than today and this adds some complexity.
Please, help. Thanks a lot in advance.

 

[CalcSux78]

I'd recommend a helper column to calulate the number of days per record. (This row can be hidden if needed)

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Office	Position	Start Date	Termination Date	Days			Office	Manager	Senior Manager	Director
2	New York	Manger	20-Jan-16	11-Nov-17	661			Berlin	497	810	812
3	Chicago	Director	20-Jan-16	28-Dec-18	810			Boston	696	0	438
4	Miami	Senior Manager	20-Jan-16	13-Feb-17	390			Chicago	0	588	682
5	Boston	Director	20-Jan-16	2-Apr-17	438			London	634	0	0
6	Toronto	Manger	20-Jan-16	20-May-19	810			Miami	0	390	0
7	Seattle	Senior Manager	20-Jan-16	8-Jul-17	535			Moscow	0	0	601
8	Paris	Director	20-Jan-16	26-Aug-19	810			New York	0	812	0
9	London	Manager	20-Jan-16	15-Oct-17	634			Paris	0	0	810
10	Berlin	Senior Manager	20-Jan-16	30-Sep-19	810			Rome	482	0	0
11	Moscow	Director	18-Jan-16	10-Sep-17	601			Seattle	0	535	0
12	Tokio	Director	18-Jan-16	11-Aug-19	812			Tokio	0	0	812
13	Rome	Manager	18-Jan-16	14-May-17	482			Toronto	0	0	812
14	New York	Senior Manager	18-Jan-16	6-Jul-19	812
15	Chicago	Senior Manager	18-Jan-16	28-Aug-17	588
16	New York	Senior Manager	18-Jan-16	21-Oct-19	812
17	Boston	Manager	18-Jan-16	14-Dec-17	696
18	Toronto	Director	18-Jan-16	7-Feb-27	812
19	Berlin	Director	18-Jan-16	3-Apr-20	812
20	Berlin	Manager	18-Jan-16	29-May-17	497
21	Chicago	Director	18-Jan-16	25-Jul-17	554
Sheet1

Cell Formulas
Range	Formula
I2	=IFERROR(AVERAGEIFS($E$2:$E$21,$A$2:$A$21,$H2,$B$2:$B$21,I$1),0)
J2	=IFERROR(AVERAGEIFS($E$2:$E$21,$A$2:$A$21,$H2,$B$2:$B$21,J$1),0)
K2	=IFERROR(AVERAGEIFS($E$2:$E$21,$A$2:$A$21,$H2,$B$2:$B$21,K$1),0)
E2	=MIN(TODAY(),D2)-C2

 

[qarat]

CalcSux78, thanks. It's an opportunity. But are there any variants without the helper column?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Office[/TD]
[TD]Position[/TD]
[TD]Start Date[/TD]
[TD]Termination Date[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]New York[/TD]
[TD]Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

11/11/2017​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Chicago[/TD]
[TD]Director[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

12/28/2018​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Miami[/TD]
[TD]Senior Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

2/13/2017​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Boston[/TD]
[TD]Director[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

4/2/2017​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Toronto[/TD]
[TD]Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

5/20/2019​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Seattle[/TD]
[TD]Senior Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

7/8/2017​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]Paris[/TD]
[TD]Director[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

8/26/2019​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]London[/TD]
[TD]Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

10/15/2017​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Berlin[/TD]
[TD]Senior Manager[/TD]
[TD]

1/20/2016​

[/TD]
[TD]

9/30/2019​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Moscow[/TD]
[TD]Director[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

9/10/2017​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Tokio[/TD]
[TD]Director[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

8/11/2019​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Rome[/TD]
[TD]Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

5/14/2017​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]New York[/TD]
[TD]Senior Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

7/6/2019​

[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Chicago[/TD]
[TD]Senior Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

8/28/2017​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]New York[/TD]
[TD]Senior Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

10/21/2019​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Boston[/TD]
[TD]Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

12/14/2017​

[/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]Toronto[/TD]
[TD]Director[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

2/7/2027​

[/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]Berlin[/TD]
[TD]Director[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

4/3/2020​

[/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]Berlin[/TD]
[TD]Manager[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

5/29/2017​

[/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]Chicago[/TD]
[TD]Director[/TD]
[TD]

1/18/2016​

[/TD]
[TD]

7/25/2017​

[/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]Office[/TD]
[TD]Manager[/TD]
[TD]Senior Manager[/TD]
[TD]Director[/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]New York[/TD]
[TD]

661​

[/TD]
[TD]

1318.5​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD]

588​

[/TD]
[TD]

813.5​

[/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]Miami[/TD]
[TD][/TD]
[TD]

390​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]Boston[/TD]
[TD]

696​

[/TD]
[TD][/TD]
[TD]

438​

[/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]Toronto[/TD]
[TD]

1216​

[/TD]
[TD][/TD]
[TD]

4038​

[/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD]Seattle[/TD]
[TD][/TD]
[TD]

535​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD]Paris[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1314​

[/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD]London[/TD]
[TD]

634​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD]Berlin[/TD]
[TD]

497​

[/TD]
[TD]

1349​

[/TD]
[TD]

1537​

[/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD]Moscow[/TD]
[TD][/TD]
[TD][/TD]
[TD]

601​

[/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD]Tokio[/TD]
[TD][/TD]
[TD][/TD]
[TD]

1301​

[/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD]Rome[/TD]
[TD]

482​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B26=IFERROR(AVERAGE(IF($A$2:$A$21=$A26,IF($B$2:$B$21=B$25,$D$2:$D$21-$C$2:$C$21))),"") control+shift+enter

Copy across and down

 

[qarat]

MARZIOTULLIO , thanks a lot! But how to add the condition that if the termination date is in future, the number of days is calculated starting from today? For example the termination date for Director in Chicago is 28 December 2018. Therefore the number of days worked should be calculated from today 09 April 2018.