Count the number of incidents within a certain date range

  • Thread starter Thread starter Fester
  • Start date Start date
F

Fester

OK, here's what I want to do. I've been racking my brain trying to
figure it out and am lost.

Here's the data

Date Contact Type

12/1/05 Phone Call
12/5/05 Internal Client
12/26/05 Internal Client
1/1/06 Phone Call

What I want to do is show how many "Phone Call" incidents during the
month of December (i.e. 1) and the number of "Internal Call" incidents
during December (i.e. 2).

I've tried to do AND statements and it doesn't work. Any help would be
appreciated.

Brendon
 
OK, here's what I want to do. I've been racking my brain trying to
figure it out and am lost.

Here's the data

Date Contact Type

12/1/05 Phone Call
12/5/05 Internal Client
12/26/05 Internal Client
1/1/06 Phone Call

What I want to do is show how many "Phone Call" incidents during the
month of December (i.e. 1) and the number of "Internal Call" incidents
during December (i.e. 2).

I've tried to do AND statements and it doesn't work. Any help would be
appreciated.

Brendon
Click to expand...

Date=NAMED column of Dates
Contact_Type = NAME'd column of contact types.

=SUMPRODUCT((MONTH(Date)=12)*(Contact_Type="Internal Client"))

is one way.

You can substitute cell references for the 12 and the Contact Type.

If you want to specify not only the month but a particular year, you can add
another factor to the function:

=SUMPRODUCT((MONTH(Date)=12)*
(YEAR(Date)=2005)*(Contact_Type="Internal Client"))

or you could do something like:

=SUMPRODUCT(Date>=DATE(2005,12,1))*(Date<=DATE(2005,12,31)*...)





--ron
 
One way

=SUMPRODUCT(--($A$2:$A$20-DAY($A$2:$A$20)+1=--"2005-12-01"),--($B$2:$B$20="P
hone Call"))

etc.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top