summing the number of dates
 

The scenario:
cell A - I have a range of date (title Date) and cell B - I have a range of
incidents (title Error Dept)
-------
Date | Error Dept
Feb 12 D
Feb 12 O
Feb 13 D
Feb 15 O
Feb 12 D
Feb 13 O

How can I formula the results that tells me there are 2 Ds in Feb 12 from
Error Department.

summing the number of dates
 

Hi,

Here's a starting point:

=SUMPRODUCT(--(MONTH(A2:A7)=D1),--(B2:B7=E1))

Where you have entered 2 in D1 and D in E1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jafferi" wrote:

The scenario:
cell A - I have a range of date (title Date) and cell B - I have a range of
incidents (title Error Dept)
-------
Date | Error Dept
Feb 12 D
Feb 12 O
Feb 13 D
Feb 15 O
Feb 12 D
Feb 13 O

How can I formula the results that tells me there are 2 Ds in Feb 12 from
Error Department.

summing the number of dates
 

Hi,

A few followups to my previous message:

1. I get 3 D's in the month of February based on your data, not the 2 you
say you get?
2. My previous example does not take into account of the year of the date.
If your dates extend across more than one year here is another formula:

=SUMPRODUCT(--(A2:A7=D1),--(A2:A7<=E1),--(B2:B7=F1))

here 2/1/2009 is entered in D1, 2/28/2009 is entered in E1 and D is entered
in F1.

3. If the dates are not Excel legal dates we will need to consider another
approach.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jafferi" wrote:

The scenario:
cell A - I have a range of date (title Date) and cell B - I have a range of
incidents (title Error Dept)
-------
Date | Error Dept
Feb 12 D
Feb 12 O
Feb 13 D
Feb 15 O
Feb 12 D
Feb 13 O

How can I formula the results that tells me there are 2 Ds in Feb 12 from
Error Department.

summing the number of dates
 

Use cells to hold the criteria you want to count for:

D1 = 2/12/2009
E1 = D

Then:

=SUMPRODUCT(--(A1:A6=D1),--(B1:B6=E1))

--
Biff
Microsoft Excel MVP


"Jafferi" wrote in message
...
The scenario:
cell A - I have a range of date (title Date) and cell B - I have a range
of
incidents (title Error Dept)
-------
Date | Error Dept
Feb 12 D
Feb 12 O
Feb 13 D
Feb 15 O
Feb 12 D
Feb 13 O

How can I formula the results that tells me there are 2 Ds in Feb 12 from
Error Department.