Number of Customers per Month over several months 1

[BxWill]

Have a MS Excel 2007 worksheet with 4 columns and over 220,000 records like the following;

AcctNbr---CustomerStartDate--CustomerEndDate--CustType
12457-----1/1/08------------5/31/08-----------ACDEV
12457-----6/1/08------------12/31/08----------ACDFA
12460-----1/1/08------------12/31/08----------ACDEV
13560-----1/1/08------------3/31/08-----------AFDCA
13560-----6/1/08------------12/31/08----------AFFFC

Need to compute the number of customers at the end of each month from January 2008 through December 2008 by CustType.

For example, AcctNbr 12457 should be counted as a customer for each month from Jan 2008 through May 2008 and then again from June 2008 through Dec 2008.

Is it possible to obtain the number of customers per month for each month in 2008?

Currently trying several variations of pivot tables without much success.

 

[Gavona]

I would create a column for each month. Column Heading would be a (proper excel) date for the end of the month. Populate these month columns with a formula along the lines:

=--and(CustomerStartDate=<MonthEndDate, CustomerEndDate>previousMonthEndDate)

The double minus sign coerces the result of the And to be 1 or 0 rather than True of False.

A couple of rows above the Month have a sum formula to give your totals.

Gavin

 

[BxWill]

Did initially use the formula displayed but received #Value! errors. The specific formula used was something like "=--(B8<=$E$6,C8>C7)"

Also, was not able to differentiate among the various customers.

It appears that I would have to include some additional If statements to distinguish among the customers.

Is there a way to perform the count using sql?

(The data within the Excel worksheet is the query result of a sql script that extracts data from a sql server database.)

Any additional thoughts?

 

[Gavona]

SQL is not something I have mastered so hopefully someone else will answer that.

I didn't realise that you needed to distinguish customers. Suggest same solution and then a pivot table to summarise.

=--(B8<=$E$6,C8>C7)

Your formula is confusing me and probably needs to be
=--[red]And[/red](B8<=[red]E[/red]$6,C8>C[red]$[/red]7)"
Assume B8 is CustomerStartDate, C8 is CustomerEndDate
E6 is month end,
C7 is previousMonthEnd (this one confuses - why not D6?)
Appropriate $ signs will help you to copy down and across.

If you did want to use formulae to distinguish customers and months then it would be

=--and(CustomerStartDate=<MonthEndDate, CustomerEndDate>previousMonthEndDate,AcctNbr=Customer)



Gavin