Prev: when i type 5 then it shows.006
Next: Count backwards N cells
From: Jum on 2 Feb 2010 01:33
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.


From: T. Valko on 2 Feb 2010 02:00
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" <Jum(a)discussions.microsoft.com> wrote in message
news:B6895EE6-1BF2-4457-A9CF-869005AE1380(a)microsoft.com...
> 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.
>
>


 | 
Pages: 1
Prev: when i type 5 then it shows.006
Next: Count backwards N cells