Count the number of incidents within a certain date range

[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

 

[Ron Rosenfeld]

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

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

 

[Bob Phillips]

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)

 

[Fester]

That is Fantastic, Thank You very much, it worked perfectly.

Brendon

 

[Ron Rosenfeld]

That is Fantastic, Thank You very much, it worked perfectly.

Brendon

You're welcome. Glad to help.
--ron