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

[Ian Mac]

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??

[Aladin Akyurek]

Ian,

What makes a record with agent X unique?

What makes a record with a certain date unique?

Aladin

[Ian Mac]

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.

[Aladin Akyurek]

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 ]

[Ian Mac]

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,