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.
|