Don't count duplicates

[RPowlett]

Hi,

I hope someone can help with this query.

I am looking to record each of my team members activity excluding duplicate results.

Here is some sample data:

Phoner Company Name Date of Meeting DD/MM/YY Month Meeting Held in Initial Meeting Type AA1F or Tel "Does the company have a DB Scheme? Y/N" Opportunity Booked on Total Fee/Bkge Consultants Name Pipeline, Work In Progress, Not Interested, Won or Lost Caller 1 T J Smith & Nephew 01/03/2015 March Telephone No Opportunity 1 Consultant 11 Not Interested Caller 1 Streamline 01/05/2015 May Face To Face Yes Opportunity 1 £10,000 Consultant 20 Won Caller 1 Streamline 03/06/2015 June Telephone No Opportunity 1 Consultant 1 Won Caller 1 Streamline 03/06/2015 June Face To Face Yes Opportunity 2 Consultant 12 Won Caller 1 Street League 01/08/2015 August Telephone No Opportunity 3 Consultant 7 Won Caller 1 T J Smith & Nephew 01/11/2015 November Face To Face Yes Opportunity 2 Consultant 3 Won Caller 1 Streamline 02/11/2015 November Face To Face Yes Opportunity 1 Consultant 4 Won Caller 1 Streamline 02/11/2015 November Telephone Yes Opportunity 2 Consultant 2 Won Caller 1 Streamline 05/12/2015 December Face To Face Yes Opportunity 1 Consultant 8 Won Caller 1 Abbott Laboratories 16/05/2015 May Face To Face Yes Opportunity 1 Consultant 20 Won Caller 1 Aberdeen Asset Management August Face To Face No Opportunity 1 Consultant 6 Work In Progress

I need to find out how many different opportunities each caller has made counting duplicate opportunities for each company only once. I would also need a formula for the total amounts of the won opportunities for each caller.

For example Caller 1 has made a total of 5 opportunities so far worth £10k.

I tried this formula for the number of opportunities but I kept getting an inaccurate number:

{=SUM(IF(FREQUENCY(IF('BDT Meetings Log'!A2:A2000=A3,IF(1-('BDT Meetings Log'!B2:B2000=""),IF(1-('BDT Meetings Log'!G2:G2000=""),MATCH('BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!B2:B2000&'BDT Meetings Log'!G2:G2000,'BDT Meetings Log'!J2:J2000="Won")))),ROW('BDT Meetings Log'!G2:G2000)-ROW('BDT Year Stats'!A3)+1),1))}

Can someone help me please?

RPowlett

 

[RPowlett]

Hi Aladin,

I've just made the adjustment you suggested for the values and that works perfectly!

Thanks for all of your help with this

RPowlett

 

[Aladin Akyurek]

Hi Aladin,

I've just made the adjustment you suggested for the values and that works perfectly!

Thanks for all of your help with this

RPowlett

You're very welcome.