Count item only once that is repeated in a given time line?

[Marq]

Using Excel 2007

Column D (D3:D2000) contains dates, to which those dates are repeated....for instance I can have ten cells in column D with todays date, 2-25-13.

Column L (L3:L2000) Will have the name of a project and much like the dates the project can be repeated multiple times....so I can have a project called P0ABC123 listed twenty times in colum L and it be associated with various dates in column D...for example I can have P0ABC123 listed 3 times and the date could be 1-30-13....then I can have it listed five more times and the dates for those five times in column D can be 2-12-13.

I would like to have a formula that says does this:

"Look in column D for date range 2-18-13 thru 2-24-13 and then look in column L and list each project worked in that range.......then in another cell give me a count of how many jobs we worked in that range."

So if I have ten P0ABC123 in the date range, only list it once.......and if that was the ONLY job I worked in that range then the other cell that does the counting will have the number 1.

This is waaayyy too complicated for me to figure out I dont even know where to begin to start writing the formula.

 

[Robert Mika]

Using Excel 2007

Column D (D3:D2000) contains dates, to which those dates are repeated....for instance I can have ten cells in column D with todays date, 2-25-13.

Column L (L3:L2000) Will have the name of a project and much like the dates the project can be repeated multiple times....so I can have a project called P0ABC123 listed twenty times in colum L and it be associated with various dates in column D...for example I can have P0ABC123 listed 3 times and the date could be 1-30-13....then I can have it listed five more times and the dates for those five times in column D can be 2-12-13.

I would like to have a formula that says does this:

"Look in column D for date range 2-18-13 thru 2-24-13 and then look in column L and list each project worked in that range.......then in another cell give me a count of how many jobs we worked in that range."

So if I have ten P0ABC123 in the date range, only list it once.......and if that was the ONLY job I worked in that range then the other cell that does the counting will have the number 1.

This is waaayyy too complicated for me to figure out I dont even know where to begin to start writing the formula.

Could you post smaple of your data?

 

[Marq]

Could you post smaple of your data?

COLUMN D COLUMN L
1/10/13 E-456
1/11/13 E-456
1/11/13 E-456
1/11/13 E-555
1/11/13 E-555
1/9/13 E-987
1/11/13 E-987
1/11/13 E-987
1/10/13 P0123ABC
1/11/13 P0123ABC
1/11/13 P0123ABC
1/11/13 P0123ABC
1/9/13 P0321CBA
1/10/13 P0321CBA
1/10/13 P0321CBA
1/10/13 P098745
1/10/13 P098746
1/9/13 PV-123
1/10/13 PV-123
1/10/13 PV-123
1/9/13 PV-ABC
1/10/13 PV-ABC
1/10/13 PV-ABC


here is what the results should be if date range 1-9-13 thru 1-11-13:

LIST of Jobs Number of Jobs
E-456 9
E-555
E-987
P0123ABC
P0321CBA
P098745
P098746
PV-123
PV-ABC

 

[Robert Mika]

COLUMN D COLUMN L
1/10/13 E-456
1/11/13 E-456
1/11/13 E-456
1/11/13 E-555
1/11/13 E-555
1/9/13 E-987
1/11/13 E-987
1/11/13 E-987
1/10/13 P0123ABC
1/11/13 P0123ABC
1/11/13 P0123ABC
1/11/13 P0123ABC
1/9/13 P0321CBA
1/10/13 P0321CBA
1/10/13 P0321CBA
1/10/13 P098745
1/10/13 P098746
1/9/13 PV-123
1/10/13 PV-123
1/10/13 PV-123
1/9/13 PV-ABC
1/10/13 PV-ABC
1/10/13 PV-ABC


here is what the results should be if date range 1-9-13 thru 1-11-13:

LIST of Jobs Number of Jobs
E-456 9
E-555
E-987
P0123ABC
P0321CBA
P098745
P098746
PV-123
PV-ABC

Excel 2010
	D	E	L	M	N	O	P	Q
1					1/9/2013		E-456	9
2	1/10/2013		E-456		1/11/2013		E-555
3	1/11/2013		E-456				E-987
4	1/11/2013		E-456				P0123ABC
5	1/11/2013		E-555				P0321CBA
6	1/11/2013		E-555				P098745
7	1/9/2013		E-987				P098746
8	1/11/2013		E-987				PV-123
9	1/11/2013		E-987				PV-ABC
10	1/10/2013		P0123ABC
11	1/11/2013		P0123ABC
12	1/11/2013		P0123ABC
13	1/11/2013		P0123ABC
14	1/9/2013		P0321CBA
15	1/10/2013		P0321CBA
16	1/10/2013		P0321CBA
17	1/10/2013		P098745
18	1/10/2013		P098746
19	1/9/2013		PV-123
20	1/10/2013		PV-123
21	1/10/2013		PV-123
22	1/9/2013		PV-ABC
23	1/10/2013		PV-ABC
24	1/10/2013		PV-ABC
25
Sheet1

Cell Formulas
Range	Formula
Q1	=SUMPRODUCT(--(LEN(P1:P14)>0))
P1	{=IFERROR(INDEX($L$2:$L$24,SMALL(IF(FREQUENCY(IF($L$2:$L$24<>"", IF(($D$2:$D$24>=$N$1)*($D$2:$D$24<=$N$2),MATCH(L$2:L$24,L$2:L$24,0))), ROW($L$2:$L$24)-ROW($D$2)+1),ROW($D$2:$D$24)-ROW($D$2)+1), ROWS($M$2:M2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Marq]

Excel 2010
	D	E	L	M	N	O	P	Q
1					1/9/2013		E-456	9
2	1/10/2013		E-456		1/11/2013		E-555
3	1/11/2013		E-456				E-987
4	1/11/2013		E-456				P0123ABC
5	1/11/2013		E-555				P0321CBA
6	1/11/2013		E-555				P098745
7	1/9/2013		E-987				P098746
8	1/11/2013		E-987				PV-123
9	1/11/2013		E-987				PV-ABC
10	1/10/2013		P0123ABC
11	1/11/2013		P0123ABC
12	1/11/2013		P0123ABC
13	1/11/2013		P0123ABC
14	1/9/2013		P0321CBA
15	1/10/2013		P0321CBA
16	1/10/2013		P0321CBA
17	1/10/2013		P098745
18	1/10/2013		P098746
19	1/9/2013		PV-123
20	1/10/2013		PV-123
21	1/10/2013		PV-123
22	1/9/2013		PV-ABC
23	1/10/2013		PV-ABC
24	1/10/2013		PV-ABC
25
Sheet1

Cell Formulas
Range	Formula
Q1	=SUMPRODUCT(--(LEN(P1:P14)>0))
P1	{=IFERROR(INDEX($L$2:$L$24,SMALL(IF(FREQUENCY(IF($L$2:$L$24<>"", IF(($D$2:$D$24>=$N$1)*($D$2:$D$24<=$N$2),MATCH(L$2:L$24,L$2:L$24,0))), ROW($L$2:$L$24)-ROW($D$2)+1),ROW($D$2:$D$24)-ROW($D$2)+1), ROWS($M$2:M2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I got it working!..thank you! works perfect

 

[Marq]

Excel 2010
	D	E	L	M	N	O	P	Q
1					1/9/2013		E-456	9
2	1/10/2013		E-456		1/11/2013		E-555
3	1/11/2013		E-456				E-987
4	1/11/2013		E-456				P0123ABC
5	1/11/2013		E-555				P0321CBA
6	1/11/2013		E-555				P098745
7	1/9/2013		E-987				P098746
8	1/11/2013		E-987				PV-123
9	1/11/2013		E-987				PV-ABC
10	1/10/2013		P0123ABC
11	1/11/2013		P0123ABC
12	1/11/2013		P0123ABC
13	1/11/2013		P0123ABC
14	1/9/2013		P0321CBA
15	1/10/2013		P0321CBA
16	1/10/2013		P0321CBA
17	1/10/2013		P098745
18	1/10/2013		P098746
19	1/9/2013		PV-123
20	1/10/2013		PV-123
21	1/10/2013		PV-123
22	1/9/2013		PV-ABC
23	1/10/2013		PV-ABC
24	1/10/2013		PV-ABC
25
Sheet1

Cell Formulas
Range	Formula
Q1	=SUMPRODUCT(--(LEN(P1:P14)>0))
P1	{=IFERROR(INDEX($L$2:$L$24,SMALL(IF(FREQUENCY(IF($L$2:$L$24<>"", IF(($D$2:$D$24>=$N$1)*($D$2:$D$24<=$N$2),MATCH(L$2:L$24,L$2:L$24,0))), ROW($L$2:$L$24)-ROW($D$2)+1),ROW($D$2:$D$24)-ROW($D$2)+1), ROWS($M$2:M2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

I want to add one more level of criteria..the whole formula wors as it should but now I only want it to count all of the jobs that only have "yes" in column "O"....column O has Yes and No listed ....so basically have the formula you created but make it only count the Yes's in column O......and this "Yes" criteria being the last level of criteria to be calculated....so the heirarchy would be..Date Range (column D)...Job count (column L)....then filter job count down to count the jobs that have "Yes" in column O.