Quickfire Formula - Count unique products

[RockandGrohl]

Hello all, I have a table of data with dates in B, names in C and Products in D.

There are 31 rows, but the expected result is 18 unique lines.


How do I arrive at 18? I need to take the cost of the mailing list, divide it by 18 and then divide that by the countifs of B, C and D to arrive at a total cost per product, but for now I need to work out formulaically how to turn the below list into 18 lines, essentially doing a formulaic de-dupe.

Tour Date Mailing list Tourname
02/11/2020 36. LG Jack Russell Bosworth Hall - Turkey & Tinsel
02/11/2020 36. LG Jack Russell Bosworth Hall - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Bosworth Hall - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Bournemouth & The New Forest - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Bournemouth & The New Forest - Turkey & Tinsel
23/11/2020 36. LG Jack Russell Bournemouth & The New Forest - Turkey & Tinsel
23/11/2020 36. LG Jack Russell Bournemouth & The New Forest - Turkey & Tinsel
07/12/2020 36. LG Jack Russell Brighton & the South Coast - Turkey & Tinsel
06/11/2020 36. LG Jack Russell Cornish Weekend - Turkey & Tinsel
27/11/2020 36. LG Jack Russell Cornish Weekend - Turkey & Tinsel
04/12/2020 36. LG Jack Russell Eastbourne House Party
04/12/2020 36. LG Jack Russell Eastbourne House Party
04/12/2020 36. LG Jack Russell Eastbourne House Party
04/12/2020 36. LG Jack Russell Eastbourne House Party
30/11/2020 36. LG Jack Russell Folkestone & Canterbury - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Folkestone & Canterbury - Turkey & Tinsel
07/12/2020 36. LG Jack Russell Folkestone & Canterbury - Turkey & Tinsel
07/12/2020 36. LG Jack Russell Folkestone & Canterbury - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Liverpool - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Liverpool - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Newquay & the Cream of Cornwall - Turkey & Tinsel
16/11/2020 36. LG Jack Russell Newquay & the Cream of Cornwall - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Newquay & the Cream of Cornwall - Turkey & Tinsel
07/12/2020 36. LG Jack Russell Newquay & the Cream of Cornwall - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
09/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
23/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
23/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel
30/11/2020 36. LG Jack Russell Scarborough, Whitby & Historic York - Turkey & Tinsel

 

[RockandGrohl]

The attached is what I need the formula to give. So for campaign "35. Irish Setter" there are 6 unique products across 8 lines. For "36. Jack Russell" there are 18 unique products across 31 lines. I need the result to say 6 and 18 in each corresponding line, so that I can implement a formula of:

[Campaign cost] / [Unique Products] / [Count of Each Product]

I hope this makes more sense now.

 

[RockandGrohl]

{=SUM(IF(($C2=$C$2:$C$849)*($D2=$D$2:$D$849)*($B2=$B$2:$B$849),1/COUNTIFS($C$2:$C$849,$C2,$B$2:$B$849,$B$2:$B$849,$D$2:$D$849,$D$2:$D$849)),0)}

Just tried this, not working. This is along the lines of what I need however.

 

[RockandGrohl]

This is now working:

=SUM(IF($C$2:$C$849=C2,(1/COUNTIFS($B$2:$B$849,$B$2:$B$849,$C$2:$C$849,$C$2:$C$849,$D$2:$D$849,$D$2:$D$849)),0))