Filter out unique additions month to month

  • Thread starter Thread starter Rookie_User
  • Start date Start date
R

Rookie_User

My goal is to demonstrate how many NEW customers are added each month. So if
a customer existed in January and also existed in February, they would only
be counted in the January total. If a customer appeared in March and not the
previous months then it would be counted only in March and not future months.
I am basically trying to determine the acceptance of a particular service.

I have two columns; one that has customer id and the other the month they
appeared. I know a long way to get what I want but am looking for a more
efficient method.
 
By date the "appeared", you mean the date the first became a customer,
right? For new customers in March, you want the entries that are greater
than or equal to March 1st. If so, try:

=sumproduct(--(a1:a1000>=date(2009,3,1)))

If you want to isolate a particular date range, use:
=sumproduct(--(a1:a1000>=date(2009,2,1)),--(a1:a1000<date(2009,3,1)))

Adjust the range to suit.

Regards,
Fred.
 
I don't think I did a good job explaining because I don't see how your
function will work. But let me try.

Column
A B
100 January
100 January
300 January
100 February
200 February
300 February
400 March
300 March

SO with this data, customer 100 and 300 were in January so there are 2
customers. Then in February the only new customer is 200, so the answer is
1. In March, there is 1 (400) because the "300" was already counted in
January. Does your function do that correctly?
 
Your data layout is different than my guess, so my proposed function won't
do what you want. For your situation, I would do the following:

1. Add a column (eg, C) which identifies whether the customer is new or not.
A new customer is one whose count in all previous months is zero. Use a
formula like:
=if(countif(a2:a4,a5)>0,"New","Old")
The count range will need to be adjusted to include all previous months.

2. Sum the new customers using:
=sumproduct(--(c2:c10="New"),--(b2:b10="February"))

I'm sure there's an array formula which will put both of these together, but
I'm not good at arrays. If you want this solution, create a new post, and be
as specific as possible as to what you want, and the data you have.

In the future, when you are replying to a post, it's a good idea to include
the previous replies, so that the message history can be followed. This
avoids people having to go back and forth between messages.

Regards,
Fred.
 
Back
Top