Count unique, based on values in other columns

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,109
Office Version
  1. 365
Platform
  1. Windows
I have been searching onthis for a couple of day and see today that a similar question is being asked.
If only pivot tables would do this.

I have 5 columns
Country, Retail Chain, Store, Product Group, Product

For completeness, what I am trying to achieve is:
  1. analyse how many different product groups each country sell
  2. analyse how many different products each country sell
  3. analyse how many different product groups each Retail chain sell by country
  4. analyse how many different products each Retail chain sell by country
  5. analyse how many different product groups each Store sell by country by Retail Chain
  6. analyse how many different products each Store sell by country by Retail Chain

the last two shouldnt need by Retail Chain, but you can never be too sure.
If I can get any one working correctly the others should be simple.

I am not sure if the UNQn colums should show 1 for every unique entry so the PT sums them or sums them each time so the PT displays MAX (as each value will be the same)

I have a series of Pivot Tables & Charts that are run over a Table something like this


CountryRetChainStoreProdGrpProdUNQ1UNQ2UNQ3UNQ4UNQ5UNQ6
IcelandChain 1Store 1Widget 1Prod A1
IcelandChain 1Store 1Widget 1Prod A2
IcelandChain 1Store 1Widget 2Prod B1
IcelandChain 1Store 2Widget 3Prod C1
IcelandChain 2Store 3Widget 1Prod A1
IcelandChain 3Store 4Widget 2Prod B1
NetherlandsChain 1Store 5Widget 1Prod A1
NetherlandsChain 4Store 6Widget 1Prod A3
NetherlandsChain 5Store 7Widget 2Prod B1
Example solution
Q.1Q.2
CountryProdGrpProducts
Iceland34
Netherlands23
Q.3Q.4
CountryRetChainProdGrpProducts
IcelandChain 134
IcelandChain 211
IcelandChain 311
NetherlandsChain 111
NetherlandsChain 411
NetherlandsChain 511

<COLGROUP><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" span=2 width=84><COL style="WIDTH: 48pt" span=5 width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 48pt" span=3 width=64><TBODY>
</TBODY>

Can anyone help
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi Martin,

Based on your sample data, is this what you require?...

Excel Workbook
ABCDEFGHIJKL
1CountryRetChainStoreProdGrpProdUNQ1UNQ2UNQ3UNQ4UNQ5UNQ6
2IcelandChain 1Store 1Widget 1Prod A1
3IcelandChain 1Store 1Widget 1Prod A2
4IcelandChain 1Store 1Widget 2Prod B1
5IcelandChain 1Store 2Widget 3Prod C1
6IcelandChain 2Store 3Widget 1Prod A1
7IcelandChain 3Store 4Widget 2Prod B1
8NetherlandsChain 1Store 5Widget 1Prod A1
9NetherlandsChain 4Store 6Widget 1Prod A3
10NetherlandsChain 5Store 7Widget 2Prod B1
11
12Example Solutions
13Q.1Q.2
14CountryProdGrpProducts
15Iceland34
16Netherlands23
17Q.3Q.4
18CountryRetChainProdGrpProducts
19IcelandChain 134
20IcelandChain 211
21IcelandChain 311
22NetherlandsChain 111
23NetherlandsChain 411
24NetherlandsChain 511
25
Sheet8


The formula in B15 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.
The formula in C19 needs entering with ctrl shift enter NOT just enter, it can then be copied across and down.

I'm unsure as to what you requre in the range F2:K10 with headers UNIQ.

Take a look here for an explanation on the above formulas....

Count unique distinct values that meet multiple criteria in excel | Get Digital Help - Microsoft Excel resource

I hope that helps.

Ak
 

Watch MrExcel Video

Forum statistics

Threads
1,113,931
Messages
5,545,087
Members
410,652
Latest member
Zot
Top