Finding Days off?

[eblake]

I have a list of agents, this list is their stats for each day, What I need to do if figure out how many days the agents have actually worked, Ive been messing around with
=SUM(IF((ISNUMBER(D1:D1000))&(C1:C1000=B2),D1:D1000))
=SUMPRODUCT(--(Data!C:C=6822),--(Data!D:D=12.81))
=COUNTIF(Data!C:C,"6822")
=SUM(IF(Data!C:C=B5,IF(Data!D:D="OFF",),))
=SUMPRODUCT((Data!C:C=B6)*(Data!D:D="OFF"))
=VLOOKUP(B7,Data!C:D,2,FALSE)

Cant seem to get the results im looking for. Below is an example 3 columns of data.
So agent extension:
5000 would result in 2 since I have time for both his days,
5001 would be 2
5002 would be 1 since I only have a tie for the second day
5004 would be 2
and so on.

Thanks in advance.

Template - Agent Stats v1.1.xls
	A	B	C	D
1	Date	Ext.	Time
2	09/01/04	5000	12.81
3	09/01/04	5001	17.50
4	09/01/04	5002	OFF
5	09/01/04	5004	18.47
6	09/01/04	5005	12.37
7	09/01/04	5006	17.73
8	09/01/04	5007	9.30
9	09/01/04	5008	OFF
10	09/01/04	5009	15.55
11	09/01/04	5010	13.55
12	09/01/04	5011	25.33
13	09/01/04	5012	13.51
14	09/01/04	5013	13.68
15	09/01/04	5014	OFF
16	09/02/04	5000	13.64
17	09/02/04	5001	16.15
18	09/02/04	5002	18.84
19	09/02/04	5004	19.44
20	09/02/04	5005	11.56
21	09/02/04	5006	19.50
22	09/02/04	5007	15.47
23	09/02/04	5008	OFF
24	09/02/04	5009	23.48
25	09/02/04	5010	15.26
26	09/02/04	5011	18.53
27	09/02/04	5012	OFF
28	09/02/04	5013	18.44
29	09/02/04	5014	13.28
Data (2)

 

[Brian from Maui]

Try,

=SUMPRODUCT(--(B2:B29=5000),--ISNUMBER(C2:C29))

No whole column references, ranges must be of the same size

 

[eblake]

Thanks Brian, that got it. I didn't realize you couldn't use whole columns. Learn something new each day.