Count number of occurrences
What I am trying to do is count the number of appointments a customer had
before the sale. A sample of my data is below. Customer Contact date Campaign Code Appointment Result Code 27200841 20060207 PR0002 RR 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060420 HK0001 TS 27200841 20060629 AA0001 RT A0500751 20060330 MC0001 PR A0500751 20060410 AA0001 TS A0500751 20060412 DM2625 SA A0500751 20060412 DM2625 SA A0500751 20060417 HK0002 FT A0500751 20060421 HK0002 FT What I need to get is the number of times the customer came in before the sale. A sale is coded as SA in the Appointment Result Code column. So for customer 27200841 I want 1 and customer A0500751 I want 2. I am struggling with what would be the best way to do this. Is there a function that would work? Would it be better to put the data in a pivot table and then use a function to get the results? Or would it be easier to do something in Access, which is where the data will be pulled from, before I export this to Excel? Any help is greatly appreciated. |
Count number of occurrences
=SUMPRODUCT(--(A2:A100="27200841"),--(D2:D100-"SA"))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "MarkM" <mark_mattson(at)yahoo.com wrote in message ... What I am trying to do is count the number of appointments a customer had before the sale. A sample of my data is below. Customer Contact date Campaign Code Appointment Result Code 27200841 20060207 PR0002 RR 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060418 DM2625 SA 27200841 20060420 HK0001 TS 27200841 20060629 AA0001 RT A0500751 20060330 MC0001 PR A0500751 20060410 AA0001 TS A0500751 20060412 DM2625 SA A0500751 20060412 DM2625 SA A0500751 20060417 HK0002 FT A0500751 20060421 HK0002 FT What I need to get is the number of times the customer came in before the sale. A sale is coded as SA in the Appointment Result Code column. So for customer 27200841 I want 1 and customer A0500751 I want 2. I am struggling with what would be the best way to do this. Is there a function that would work? Would it be better to put the data in a pivot table and then use a function to get the results? Or would it be easier to do something in Access, which is where the data will be pulled from, before I export this to Excel? Any help is greatly appreciated. |
All times are GMT +1. The time now is 09:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com