Can anyone me help figure this out??

[Guest]

Here is my problem:

I need to show how many days in a month a team member saw a customer. Each
day a team member could see multiple customers. I just need the number of
days per month he saw a customer. I have over 20 team members and thousands
of customers. This data runs for 10,000 rows. I can't figure this one out.
Any help is greatly appreciated!! Here is how the data is set-up:

Date Team Member Customer
1/1/2007 John Bill
1/1/2007 John Jerry
1/2/2007 John Karen

Thanks in advance!!

 

[Phil Smith]

Add coloumns for month and year, using the date as the basis. The
Formulas are month() and year(). Silly, huh?

SO your spreadsheet now looks like

Date Year month Team Member Customer.

Sort by date, customer.

Then use subtotals, breaking on Customer.

The only thing that can break this is if Joe saw Allen, and the very
next John saw Allen. Since the customer did not change, it will not
break on the new rep. IF this may be a problem, add another field,
which combines Team Member and Customer with the "&" symbol. Subtotal
break on this instead.

Other option, Pivot Table. You will still need to seperate out Month
and Year though.

 

[Guest]

Perhaps this will work. Sort the sheet by Team Member then Date. Subtotal
the data for each team member by change in date use count function and add
subtotal to team member.

Hope this helps,
Chad

 

[Sandy Mann]

You could use a SUMPRODUCT() formula:

=SUMPRODUCT((C8:C10008="John")*(MONTH(A8:A10008)=1)*(D810008="Bill"))

But with 10,000 rows of data it may be bit slow.

You could also use a Pivot table whch is much faster.

I would suggest something like:

With your data as given in columns A, B & C from Row 7 down, insert a new
Column B labelled in B7 as Month with the formula =A8 in Cell B8 and copied
down. Then creat a Pivot Table with Month & Team Member dragged to the Row
Field and Customer dragged to the Column & Data Fields making sure that the
Data shows a "Count of Customer"

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk