Counting Duplicates

[Dallan1361]

Hi all - thanks in advance for any help given it is much appreciated

I have a large dataset c.100,000 rows. It is essentially looking at costs of items and expenditure. Here is a mock-up of what I am working with to give a picture:

Country	Holiday Product	Cost
Spain	Snorkelling	£50
Spain	Booze cruise	£25
France	Snorkelling	£50
France	Volleyball	£10
France	Snorkelling	£50
France	Guided Tour	£15
Germany	Guided Tour	£15
Germany	Guided Tour	£15
Germany	Booze cruise	£25

Apologies in advance if I am massively over-complicating this, but ideally I want something that will manipulate the data into something like the below and highlight frequency of duplicates in each Country

Country	Holiday Product	Cost	Frequency
Spain	Snorkelling	£50	1
Spain	Booze cruise	£25	1
France	Snorkelling	£100	2
France	Volleyball	£10	1
France	Guided Tour	£15	1
Germany	Guided Tour	£30	2
Germany	Booze cruise	£25	1

I have this thanks to another user, 'Fluff', which will populate the first 3 columns :
'=LET(u,UNIQUE(FILTER(A2:B10000,A2:A10000<>"")),HSTACK(u,SUMIFS(C:C,A:A,INDEX(u,,1),B:B,INDEX(u,,2))))

So I would be looking for something that will populate the frequency column by each country, i.e. Snorkelling appears 1 time in Spain and 2 in France, not 3 times total

Thank you in advance

 

[Fluff]

How about

=LET(u,UNIQUE(FILTER(A2:B10000,A2:A10000<>"")),HSTACK(u,SUMIFS(C:C,A:A,INDEX(u,,1),B:B,INDEX(u,,2)),COUNTIFS(A:A,INDEX(u,,1),B:B,INDEX(u,,2))))

 

[Cubist]

You can use the Pivot table.

 

[Dallan1361]

How about

=LET(u,UNIQUE(FILTER(A2:B10000,A2:A10000<>"")),HSTACK(u,SUMIFS(C:C,A:A,INDEX(u,,1),B:B,INDEX(u,,2)),COUNTIFS(A:A,INDEX(u,,1),B:B,INDEX(u,,2))))

Amazingly helpful once again, thank you very much!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Dallan1361]

You can use the Pivot table.
View attachment 112599

Thanks for your response! it works, but unfortunately not usable as a dataset for myself.