Count my Customers

[DaveMoore]

Hi.
I use my spreadsheet as a database containing some 15,000
transactions.
The fields include Customer No. in column 'L' and a Period field in
column 'C'. The Customer No. may be alphabetical, numeric or
alphanumeric. The period is the year. An individual customer may
have anything from 1 to 50 transactions in the database which is
sorted by Customer No. With this forums help I have successfully
counted the number of transactions by period, and totalled the value
by period.

I would now like to count the total number of different customers I
have within the database and also in a transaction period.

Can anyone help?

Regards,
Dave Moore

 

[Bernie Deitrick]

Dave,

To count unique customers, array enter (enter using Ctrl-Shift-Enter) a formula like

=SUM(1/COUNTIF(L2:L1000,L2:L000))

To count unique customers for a transaction period, you could use a helper column, say M, where you
enter the period of interest in M1 (matching how the entry is made in column C), and in M2, enter
the formula

=IF(C2=$M$1,1/SUMPRODUCT(($C$2:$C$1000=$M$1)*($L$2:$L$1000=L2)),0)

and copy down. Then use

=SUM(M2:M1000)

to give the count of unique customers for that period.

 

[DaveMoore]

Thank You so much Bernie.

Dave,

To count unique customers, array enter (enter using Ctrl-Shift-Enter) a formula like

=SUM(1/COUNTIF(L2:L1000,L2:L000))

To count unique customers for a transaction period, you could use a helper column, say M, where you
enter the period of interest in M1 (matching how the entry is made in column C), and in M2, enter
the formula

=IF(C2=$M$1,1/SUMPRODUCT(($C$2:$C$1000=$M$1)*($L$2:$L$1000=L2)),0)

and copy down.  Then use

=SUM(M2:M1000)

to give the count of unique customers for that period.

--
HTH,
Bernie
MS Excel MVP









- Show quoted text -

 

[Bernie Deitrick]

You're quite welcome - I hope that it actually worked!

HTH,
Bernie
MS Excel MVP


Thank You so much Bernie.

 

[Bob Bridges]

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.