Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Sumproduct, unique records............. with a twist

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    All,

    I have the following type of data:

    Part1

    {"PROSPECT_NO","CREATION_DATE","STATE_CODE","PAY_METHOD","CONTRACT_TYPE","TITLE","FIRST_NAME","SURNAME","ADDRESS","AGBNT_ID","AGNET_NAME","CANCELLATION_DATE","CANCELLATION_REASON","HOW_THEY_CANCELLED";"SDTS0060036104",37274,"CANC","MDD","GAS","Mrs","Jane","Doe","2 Mystery Way, KT3 5ED","SIT00005","AGENT1",37280,"No reason given","Letter";"SDTS0060037925",37279,"CANC",0,"ELEC","Mr & Mrs","S","Madeup","12 Nowhere Street, TW135HY","SIT00008","AGENT2",37281,"Info only","Inbound call";"SDTS0060037925",37279,"CANC",0,"GAS","Mr & Mrs","S","Madeup","12 Nowhere Street, TW135HY","SIT00008","AGENT2",37281,"Info only","Inbound call";"SDTS0060037626",37278,"CANC","MDD","GAS","Mr","STEPHEN","NOT","76 As Above, OZ12 9SS","SIT00009","AGENT3",37281,"Customer changed mind","Inbound call"}

    Part2

    {"SDTS0060037412",37277,"CANC",0,"ELEC","Mr & Mrs","E","YOU","5 My Lane, ZZ26ZZ","SIT00008","AGENT4",37281,"Info only","Welcome call";"SDTS0060037412",37277,"CANC",0,"GAS","Mr & Mrs","E","YOU","5 My Lane, ZZ26ZZ","SIT00008","AGENT4",37281,"Info only","Welcome call";"SDTS0060042877",37279,"CANC",0,"ELEC","Mrs","IAN","MAC","13 Nearly There, NT104AR","SIT00008","AGENT2",37284,"Customer changed mind","Inbound call";"SDTS0060042877",37279,"CANC",0,"GAS","Mrs","IAN","MAC","13 Nearly There, NT104AR","SIT00008","AGENT2",37284,"Customer changed mind","Inbound call";"SDTS0060038094",37280,"CANC",0,"ELEC","Mrs","COMEON","EILEEN","63 Back Lane, BB5 3BB","SIT00001","AGENT1",37285,"Wants to remain with existing supplier","Inbound call"}

    What I'd like is to have a formula that will count the entries using certain criteria BUT only unique Entries

    SO, example expected results would be:

    All Cancellations by agent2 = 2
    All Cancellations on or before Jan 24 = 4

    Any ideas??


    "Have a good time......all the time"
    Ian Mac

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,656
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Ian,

    What makes a record with agent X unique?

    What makes a record with a certain date unique?

    Aladin


  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry for not explaining, I will probably test the PROSPECT_NO.

    what it is going to be used for is cancellations of utilities sales, so if a person buy both gas and electricity it counts as only 1 Cancelation, the same as if they only bought gas or electricity, but only 1. In each case of a duel sale the PROSPECT_NO will be the same.

    i.e. if any formula proposed finds a duplicate PROSPECT_NO then deduct that extra one from the findings.
    "Have a good time......all the time"
    Ian Mac

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,656
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-25 11:38, Ian Mac wrote:
    Sorry for not explaining, I will probably test the PROSPECT_NO.

    what it is going to be used for is cancellations of utilities sales, so if a person buy both gas and electricity it counts as only 1 Cancelation, the same as if they only bought gas or electricity, but only 1. In each case of a duel sale the PROSPECT_NO will be the same.

    i.e. if any formula proposed finds a duplicate PROSPECT_NO then deduct that extra one from the findings.
    Ian,

    What follows assumes that CANCELLATION_DATE is blank when a utilities sales is not cancelled.

    Array-enter in order to answer the query All Cancellations by agent2:

    =SUM(IF((K8:K16="AGENT2")*(LEN(L8:L16)>0),1/COUNTIF(A8:A16,A8:A16)))

    where K8:K16 houses AGENT_NAMEs, L8:L16 CANCELLATION_DATEs, and A8:A16 PROSPECT_NOs.

    This gives me indeed 2 for AGENT2.

    Array-enter in order to answer the query All Cancellations on or before Jan 24:

    =SUM(IF(L8:L16<="24-Jan-02"+0,1/COUNTIF(A8:A16,A8:A16)))

    where ranges are as described above.

    This gives me 1, because there is only one cancellation date that meets the condition <= 24-Jan-02. All other dates are later than this criterion date.

    You could also try set up Advanced Filter or use database functions to answer the above queries.

    Use also dynamically computed ranges (I guess you do that already for dynamically changing data areas).

    Regards,

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-26 01:38 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-25 12:16, Aladin Akyurek wrote:

    Ian,

    What follows assumes that CANCELLATION_DATE is blank when a utilities sales is not cancelled.

    Array-enter in order to answer the query All Cancellations by agent2:

    =SUM(IF((K8:K16="AGENT2")*(LEN(L8:L16)>0),1/COUNTIF(A8:A16,A8:A16)))

    where K8:K16 houses AGENT_NAMEs, L8:L16 CANCELLATION_DATEs, and A8:A16 PROSPECT_NOs.

    This gives me indeed 2 for AGENT2.

    Array-enter in order to answer the query All Cancellations on or before Jan 24:

    =SUM(IF(L8:L16<="24-Jan-02"+0,1/COUNTIF(A8:A16,A8:A16)))

    where ranges are as described above.

    This gives me 1, because there is only one cancellation date that meets the condition <= 24-Jan-02. All other dates are later than this criterion date.

    You could also try set up Advanced Filter or use database functions to answer the above queries.

    Use also dynamically computed ranges (I guess you do that already for dynamically changing data areas).

    Regards,

    Aladin

    [ This Message was edited by: Aladin Akyurek on 2002-03-26 01:38 ]
    But of course! (didn't even think of just using the COUNTIF() in there with any other criteria), Thanks VERY much again, I adapted the first one as the list in question is JUST cancellations therefore no need to eliminate blanks (will use it elsewhere I'm sure).

    I thought you may have came back with a SUMPRODUCT() answer as you seem to have moved away from CSE formulas as and when possible (I assume it wasn't).

    Many thanks again,
    "Have a good time......all the time"
    Ian Mac

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •