Count my Customers
If this is a one-time count, one way to do it is to make some extra columns
to spot duplicate customer numbers. I usually do that this way:
1) Sort the database by customer number (which you've already done).
2a) In a helping column, say X, put the formula =L2=L1. With the worksheet
sorted on col L, that puts a FALSE on the first appearance of each customer
number and a TRUE on all the duplicates. You can then use COUNTIF(X:X,FALSE)
to see how many unique customer numbers are in the worksheet.
Alternatively...
2b) ...have your helping column X say =INT(L2<L1), which gives you a 1 for
each unique customer number and a 0 for each duplicate. Then you can use
SUM(X:X) to get the customer count.
To get the number of customers in a given period, modify 2B above to read
=INT(L2&C2<L1&C1) (that gives you customers that are unique within each
period) and =SUMIF(C:C,<Year,L:L); that sums up the 1s only for the year you
specified.
If this is NOT a one-time count - if you want to do it on a regular basis, I
suggest you start keeping extra worksheets indexing customers, at least. But
let's see how this works for you first.
|