Count my Customers
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 |
Count my Customers
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 "DaveMoore" wrote in message ... 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 |
Count my Customers
Thank You so much Bernie.
On 19 Jan, 18:32, "Bernie Deitrick" <deitbe @ consumer dot org wrote: 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 "DaveMoore" wrote in message ... 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- Hide quoted text - - Show quoted text - |
Count my Customers
You're quite welcome - I hope that it actually worked!
HTH, Bernie MS Excel MVP "DaveMoore" wrote in message ... Thank You so much Bernie. |
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. |
All times are GMT +1. The time now is 03:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com