Count unique, based on values in other columns

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,141
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0

Forum statistics

Threads
1,212,936
Messages
6,110,764
Members
448,297
Latest member
cocolasticot50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top