Creating Aggregate Data by Customer Name for Specific Date Range

[jseymour84]

Hello,

I am having some problems with Excel 2007 - specifically my lack of
knowledge with the product. I have service call data from a range of
3 years for about 15 different customers. I need to use this data to
answer two different questions:

1. I need to count the number cases throughout the entire date range
by customer.
2. I need to count the number of cases throughout the entire date
range by customer by month.

For example, I need to see how many service calls Customer A had in
the last three years as well as per month for the last three years.

My sheet looks like this:

A1 B1
9/11/2007 Customer A
10/1/2005 Customer B

(Names have been changed for privacy concerns).

I've tried using COUNTIF and FREQUENCY but cannot seem to generate
output like I want it to. I've tried importing the data into Access
but that was even more futile than the COUNTIF function was.

Does anyone have any suggestions?

Thanks,
Jesse

 

[Mike H.]

I would do it with a simple vba sub. But you can also do it with a pivot
table. First, I would add a couple things. Add headings to each column
(Name, Date, MMYYYY). Then I'd create a new column called MMYYYY and would
put this calculation in that column:

=RIGHT("0"&MONTH(C10),2)&YEAR(C10)

Where C10 is the date column. Then I'd have the cursor in this "table" of
data and
select Data>Pivottable and PivotChart report. Then click next 2 times. and
then click the layout button. Put Customer in the Row gray area, put
customer in the Data area and put MMYYYY in the column area. That will
answer your second question. By manually adjusting the data that is
appearing on the pivottable itself, (click the pulldown next to the MMYYYY
field and you can make adjustments, then you can answer your first question.
HTH

 

[jseymour84]

Mike,

Thank you for the PivotTable suggestions - that hit the nail squarely
on the head. With my PivotTable I can select a name and then a date
range or all to answer either question.

Thanks again,

Jesse