Count a value excluding duplicates depending on a set criteria
 

Hi, hope someone can help,

I have data that has multiple entrys for a date, but I only want to count
each day as one using a formula in excel 2007.

A B C D
1 Dept Crew Type Date
2 301 A 1 1/02/2009
3 302 C 2 1/02/2009
4 301 A 1 1/02/2009
5 301 A 1 2/02/2009
6 302 C 2 1/02/2009
7 303 D 2 1/02/2009
8 301 B 1 9/02/2009
9 301 A 2 1/02/2009
10 303 D 2 9/02/2009

E.g. I want to know how many days a crew worked in the above, 'Dept' =
"301", 'Crew' = "A", 'Type' = "1", how many days. Would equal 2 days.

Count a value excluding duplicates depending on a set criteria
 

One way...

Array entered** :

=COUNT(1/FREQUENCY(IF(A2:A10=301,IF(B2:B10="A",IF(C2:C10=1, D2:D10))),D2:D10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Jum" wrote in message
...
Hi, hope someone can help,

I have data that has multiple entrys for a date, but I only want to count
each day as one using a formula in excel 2007.

A B C D
1 Dept Crew Type Date
2 301 A 1 1/02/2009
3 302 C 2 1/02/2009
4 301 A 1 1/02/2009
5 301 A 1 2/02/2009
6 302 C 2 1/02/2009
7 303 D 2 1/02/2009
8 301 B 1 9/02/2009
9 301 A 2 1/02/2009
10 303 D 2 9/02/2009

E.g. I want to know how many days a crew worked in the above, 'Dept' =
"301", 'Crew' = "A", 'Type' = "1", how many days. Would equal 2 days.