Filter out unique additions month to month

[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.

 

[Fred Smith]

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.

 

[Rookie_User]

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?

 

[Fred Smith]

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.