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

 

[jasonb75]

Please use XL2BB to post your example so that the data is aligned correctly (see link with full guide in my signature block below).

 

[RockandGrohl]

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

I've done it like this, I can't use that as am on a work PC. Hope it improves things.

 

[jasonb75]

It would be easiest with a helper column. With excel 2010, a single cell formula is going to be both inefficient and messy.

Book1
	A	B	C	D	E
1	Tour Date	Mailing list	Tourname
2	02/11/2020	36. LG Jack Russell	Bosworth Hall - Turkey & Tinsel	1	18
3	02/11/2020	36. LG Jack Russell	Bosworth Hall - Turkey & Tinsel	2
4	09/11/2020	36. LG Jack Russell	Bosworth Hall - Turkey & Tinsel	1
5	09/11/2020	36. LG Jack Russell	Bournemouth & The New Forest - Turkey & Tinsel	1
6	09/11/2020	36. LG Jack Russell	Bournemouth & The New Forest - Turkey & Tinsel	2
7	23/11/2020	36. LG Jack Russell	Bournemouth & The New Forest - Turkey & Tinsel	1
8	23/11/2020	36. LG Jack Russell	Bournemouth & The New Forest - Turkey & Tinsel	2
9	07/12/2020	36. LG Jack Russell	Brighton & the South Coast - Turkey & Tinsel	1
10	06/11/2020	36. LG Jack Russell	Cornish Weekend - Turkey & Tinsel	1
11	27/11/2020	36. LG Jack Russell	Cornish Weekend - Turkey & Tinsel	1
12	04/12/2020	36. LG Jack Russell	Eastbourne House Party	1
13	04/12/2020	36. LG Jack Russell	Eastbourne House Party	2
14	04/12/2020	36. LG Jack Russell	Eastbourne House Party	3
15	04/12/2020	36. LG Jack Russell	Eastbourne House Party	4
16	30/11/2020	36. LG Jack Russell	Folkestone & Canterbury - Turkey & Tinsel	1
17	30/11/2020	36. LG Jack Russell	Folkestone & Canterbury - Turkey & Tinsel	2
18	07/12/2020	36. LG Jack Russell	Folkestone & Canterbury - Turkey & Tinsel	1
19	07/12/2020	36. LG Jack Russell	Folkestone & Canterbury - Turkey & Tinsel	2
20	30/11/2020	36. LG Jack Russell	Liverpool - Turkey & Tinsel	1
21	30/11/2020	36. LG Jack Russell	Liverpool - Turkey & Tinsel	2
22	09/11/2020	36. LG Jack Russell	Newquay & the Cream of Cornwall - Turkey & Tinsel	1
23	16/11/2020	36. LG Jack Russell	Newquay & the Cream of Cornwall - Turkey & Tinsel	1
24	30/11/2020	36. LG Jack Russell	Newquay & the Cream of Cornwall - Turkey & Tinsel	1
25	07/12/2020	36. LG Jack Russell	Newquay & the Cream of Cornwall - Turkey & Tinsel	1
26	09/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	1
27	09/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	2
28	09/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	3
29	23/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	1
30	23/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	2
31	30/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	1
32	30/11/2020	36. LG Jack Russell	Scarborough, Whitby & Historic York - Turkey & Tinsel	2
Sheet1

Cell Formulas
Range	Formula
E2	E2	=COUNTIF(D:D,1)
D2:D32	D2	=COUNTIFS(A$2:A2,A2,B$2:B2,B2,C$2:C2,C2)

 

[RockandGrohl]

Thanks - does that formula need to have the data ordered in a certain way, because I can't guarantee data will come in by Name then Date.

I think for this particular application having one formula in a cell would be beneficial.

Cheers.

 

[jasonb75]

It can be in any order, the helper column is only looking for the first occurrence of the date / mailing list / name combination regardless of where it occurs.

I'll have a look at doing a single cell formula later but if you still need it to work with 2010 then it is unlikely to be practical.

 

[RockandGrohl]

=SUMPRODUCT((1/COUNTIFS(B2:B32,B2:B32,C2:C32,C2:C32,D2:D32,D2:D32)))

This formula results in "18", and is calculated immediately


I need it to be in one formula because I'm actually doing Cost / # of unique products / times product appears

So if cost if £10,000, it's divided by 18 and then divided by 2 for the first date of Bosworth Hall as there's 2 products, and then 1 for the second date of Bosworth as there's one product.

Thanks... do I... do I mark my own answer as correct? :'D

 

[RockandGrohl]

BELAY THAT REMARK!

I have 847 lines across 4 campaigns, but that formula does not work when written like this:

=SUMPRODUCT((1/COUNTIFS($B3:$B850,$B3:$B850,$C3:$C850,$C3:$C850,$D3:$D850,$D3:$D850)))

The data range is columns B, C and D, from row 3 to row 850.

I'm getting a result of 596 - when It should be 18 for one campaign, and then 6 for another and 20 for another, etc.

 

[jasonb75]

You say the expected result is 18, but the way that I read your last 2 posts is that rows 2 and 3 (based on the table in my previous reply) should both be 10000/18/2 and row 4 should be 10000/18/1? Meaning that a result is needed per row rather than a total.

Is that correct or am I missing something?

I'm going to be afk for the rest of the day, if it doesn't get picked up and solved by another member then I'll take another look tonight.

 

[RockandGrohl]

So on one sheet, I have hundreds of rows of Mailing Campaigns. These campaigns have a set of products in and the products have a set of dates attached to them.

I need to know the number of unique products, per campaign.

In the top example, "Bosworth hall Turkey & Tinsel", there are three lines, but this is only two products as there are two products on the 02/11/2020


So for campaign number 36, there are 31 lines but 18 unique products and the unique products are derived from a de-duplication of columns B, C and D.


What I'm trying to do in a single cell is calculate the amount of unique lines that make up each campaign. Campaign 36 has 31 lines and 18 unique products, so I'm dividing the cost of the campaign amongst the 18 products and then dividing that per-cost amongst each of the duplicate lines (if any)

That gives me proportional spend per product per campaign.


The trouble is, the formula I provided above works perfectly when there's only one campaign in a data set. If more campaigns are added - like when I have 850 lines in total across 4 campaigns, instead of giving me 4 distinct numbers it just gives me the total amount of distinct products across every campaign which is 596.