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

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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??
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top