Array based formula help

[Magoosball]

Below is a sample of my data

Table 1:

	From Date	To Date	Yes or blank
Employee 1
	11/26/2001	12/8/2001	Yes
	1/7/2002	1/22/2002	Yes
	6/17/2002	7/15/2002
	10/5/1995	1/1/2000	Yes
Employee 2
	10/21/2004	12/18/2004	Yes
	2/25/2005	3/4/2005
	3/6/2005	3/26/2005	Yes
	1/8/2006	2/4/2006
	2/23/2006	2/25/2006	Yes
	5/31/2006	6/5/2006

<tbody>
</tbody>

From the above data I need to write 3 formulas to get the answer table below:

Table 2:

Employee Number	Min "Yes" Date	Max "Yes" Date	Number of "Non blanks"
Employee 1	10/05/1995	1/7/2002	3
Employee 2	10/21/2004	2/23/2006	3

<tbody>
</tbody>

Please note the formulas need to first find the employee number on table 2 in table 1 and the array of employment dates that match the employee number number.

The formula is looking to find the min and max from date for each employee that ins't blank in the 4th column. It is also looking to count the number of Non blank cells in column 4 for each employee



Thank you in advance!

 

[RasGhul]

Hi Magoos,

Maybe you could use these, note I had to use a helper column but that can be hidden if needed;

Book1
	A	B	C	D	E
1		From Date	To Date	Yes or blank	Helper
2	Employee 1				Employee 1
3		26/11/2001	8/12/2001	Yes	Employee 1
4		7/01/2002	22/01/2002	Yes	Employee 1
5		17/06/2002	15/07/2002		Employee 1
6		5/10/1995	1/01/2000	Yes	Employee 1
7	Employee 2				Employee 2
8		21/10/2004	18/12/2004	Yes	Employee 2
9		25/02/2005	4/03/2005		Employee 2
10		6/03/2005	26/03/2005	Yes	Employee 2
11		8/01/2006	4/02/2006		Employee 2
12		23/02/2006	25/02/2006	Yes	Employee 2
13		31/05/2006	5/06/2006		Employee 2
14
15
16	Employee Number	Min "Yes" Date	Max "Yes" Date	Number of "Non blanks"
17	Employee 1	5/10/1995	7/01/2002	3
18	Employee 2	21/10/2004	23/02/2006	3
Sheet1

Cell Formulas
Range	Formula
E2	=IF(A2="",E1,A2)
D17	=COUNTIFS($E$2:$E$13,A17,$D$2:$D$13,"?*")
A17	{=IFERROR(INDEX($A$2:$A$13,SMALL(IF($A$2:$A$13>0,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS($A$17:A17))),"")}
B17	{=MIN(IF($D$2:$D$13&$E$2:$E$13="Yes"&$A17,$B$2:$B$13))}
C17	{=MAX(IF($D$2:$D$13&$E$2:$E$13="Yes"&$A17,$B$2:$B$13))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H
1		From Date	To Date	Yes or blank	Employee Number	Min "Yes" Date	Max "Yes" Date	Number of "Non blanks"
2	Employee 1	11/26/2001	12/8/2001	Yes	Employee 1	10/5/1995	1/7/2002	3
3	Employee 1	1/7/2002	1/22/2002	Yes	Employee 2	10/21/2004	2/23/2006	3
4	Employee 1	6/17/2002	7/15/2002
5	Employee 1	10/5/1995	1/1/2000	Yes
6	Employee 2	10/21/2004	12/18/2004	Yes
7	Employee 2	2/25/2005	3/4/2005
8	Employee 2	3/6/2005	3/26/2005	Yes
9	Employee 2	1/8/2006	2/4/2006
10	Employee 2	2/23/2006	2/25/2006	Yes
11	Employee 2	5/31/2006	6/5/2006
Sheet1

Note the modification to the data.

In F2 control+shift+enter, not just enter, and copy down:

=MIN(IF($A$2:$A$11=E2,IF($D$2:$D$11="yes",$B$2:$B$11)))

In G2 control+shift+enter, not just enter, and copy down:

=MAX(IF($A$2:$A$11=E2,IF($D$2:$D$11="yes",$B$2:$B$11)))

In H2 just enter and copy down:

=COUNTIFS($A$2:$A$11,E2,$D$2:$D$11,"?*")