View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bridges[_2_] Bob Bridges[_2_] is offline
external usenet poster
 
Posts: 257
Default 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.