Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
=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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Countif for the number of a specific count? | Excel Worksheet Functions | |||
how do I count the number of times text in column A matches text i | Excel Worksheet Functions | |||
Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
How to count the number of times something occurs within a certain month | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) |