# Count unique values with multiple criteria and exclusions

#### AlexandraT

##### Board Regular
Hello,

I don't usually have to actually post a question here since I can usually find the answers I need in other posts. However, I am unable to find an answer now.

Here is my sample table:

ColourArticleSizeSold
Green
 Dress

<tbody>
</tbody>
2
 Apr-15

<tbody>
</tbody>
Green
 Jumper

<tbody>
</tbody>
2
 Feb-15

<tbody>
</tbody>
Green
 Jumper

<tbody>
</tbody>
1
 Feb-15

<tbody>
</tbody>
Green
 Pants

<tbody>
</tbody>
1
 May-15

<tbody>
</tbody>
Green
 Skirt

<tbody>
</tbody>
2
 May-15

<tbody>
</tbody>
Green
 Skirt

<tbody>
</tbody>
1
 Apr-15

<tbody>
</tbody>
Green
 T-shirt

<tbody>
</tbody>
1
 Feb-15

<tbody>
</tbody>
GreenT-shirt2
 Feb-15

<tbody>
</tbody>
Red
 Jumper

<tbody>
</tbody>
1
 May-15

<tbody>
</tbody>
Red
 Jumper

<tbody>
</tbody>
2
 Mar-15

<tbody>
</tbody>
Red
 Pants

<tbody>
</tbody>
1
 Apr-15

<tbody>
</tbody>
Red
 Pants

<tbody>
</tbody>
2
 Feb-15

<tbody>
</tbody>
Red
 Shorts

<tbody>
</tbody>
2
 Mar-15

<tbody>
</tbody>
RedSkirt1
 May-15

<tbody>
</tbody>
RedSocks1
 May-15

<tbody>
</tbody>
RedSocks2
 May-15

<tbody>
</tbody>

<tbody>
</tbody>

Looks a bit silly but it should help with that I need. So what I would need is to know how many types of clothes are green but not red and vice versa. I would also like these split by month. Also, how many green only types of clothes come in both sizes and how many red only types of clothes come in both sizes. Also by month.

To illustrate what I need (without taking into account the month):

 Green only 2 (Dress,T-shirt) Red only 2 (Shorts,Socks) Red 2 sizes 1 (Socks) Green 2 sizes 1 (T-shirt)

<tbody>
</tbody>

My data is some thousand rows long but there are 2 colours, 2 sizes and 4 months. There are thousands of types of clothes though.

I don't have any preference how this can be done, be it by additional columns, formula, pivot table or power pivot. But for the life of me I cannot figure it out and I can't seem to find anything online (granted I might not be searching properly).

Any help would be greatly appreciated. Thanks

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
seems ideal for a pivot table or two

seems ideal for a pivot table or two

I tried with both pivot and power pivot. distinct count would seem to me be an option but I don't know how to make it count reds that are not green. The farthest I got was to get a distinct count for red but this included articles that were both red and green. This is what I used: =CALCULATE(DISTINCTCOUNT(Table1[Article]),Table1[Colour]="Red"). I also tried =CALCULATE(DISTINCTCOUNT(Table1[Article]),Table1[Colour]="Red",Table1[Colour]<>"Green") but it makes no difference not to mention I think it might not even make sense.

dumped it into excel
selected the data and named it pivot

inserted a pivot and used pivot as the source

i added to the field list in the following order
sold
article
colour
size
all as row labels

i prefer to set my pivots up as
allow multiple filters
classic pivot table layout

then for values
I used count of articles

 Count of Article Sold Article Colour Size Total Feb-15 Jumper Green 1 1 2 1 Green Total 2 Jumper Total 2 Pants Red 2 1 Red Total 1 Pants Total 1 T-shirt Green 1 1 2 1 Green Total 2 T-shirt Total 2 Feb-15 Total 5

<colgroup><col style="mso-width-source:userset;mso-width-alt:3264;width:77pt" width="102"> <col style="mso-width-source:userset;mso-width-alt:3712;width:87pt" width="116"> <col style="width:54pt" width="72"> <col style="mso-width-source:userset;mso-width-alt:1568;width:37pt" width="49"> <col style="mso-width-source:userset;mso-width-alt:1344;width:32pt" width="42"> </colgroup><tbody>
</tbody>

Try this:-
Results Start "E1"
Code:
``````[COLOR=navy]Sub[/COLOR] MG17Jun09
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range, k [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Str [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
[COLOR=navy]End[/COLOR] If
[COLOR=navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Else[/COLOR]
Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) & ", " & Dn.Offset(, 2)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
Range("F1").Resize(, 3).Value = Array("Colour", "Type", "Sizes")
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.Keys
For Each p In Dic(k)
c = c + 1
Cells(c, "F") = k
Cells(c, "G") = p
Cells(c, "H") = "Sizes :- " & Dic(k).Item(p)
[COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]``````
Regards Mick

only t-shirt is the one that is green without being red. both jumper and pants are green but there are also red jumpers and pants on the list

Try this:-
Results Start "E1"
Code:
``````[COLOR=navy]Sub[/COLOR] MG17Jun09
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range, k [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, c [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Str [COLOR=navy]As[/COLOR] [COLOR=navy]String,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dic [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
[COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] Dic(Dn.Value) = CreateObject("Scripting.Dictionary")
[COLOR=navy]End[/COLOR] If
[COLOR=navy]If[/COLOR] Not Dic(Dn.Value).exists(Dn.Offset(, 1).Value) [COLOR=navy]Then[/COLOR]
[COLOR=navy]Else[/COLOR]
Dic(Dn.Value).Item(Dn.Offset(, 1).Value) = Dic(Dn.Value).Item(Dn.Offset(, 1).Value) & ", " & Dn.Offset(, 2)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
Range("F1").Resize(, 3).Value = Array("Colour", "Type", "Sizes")
c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.Keys
For Each p In Dic(k)
c = c + 1
Cells(c, "F") = k
Cells(c, "G") = p
Cells(c, "H") = "Sizes :- " & Dic(k).Item(p)
[COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]``````
Regards Mick

While I have no idea how a macro usually works, I believe this one returns the sizes for every article. I need just a headcount of all red article types that are not also green and viceversa.

This is the result that the code Produces.
Code:
``````[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(F) [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(G)  [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(H)           [/B][/COLOR]
1.      Colour  Type     Sizes
2.      Green   Dress    Sizes :- 2
3.      Green   Jumper   Sizes :- 2, 1
4.      Green   Pants    Sizes :- 1
5.      Green   Skirt    Sizes :- 2, 1
6.      Green   T-shirt  Sizes :- 1, 2
7.      Red     Jumper   Sizes :- 1, 2
8.      Red     Pants    Sizes :- 1, 2
9.      Red     Shorts   Sizes :- 2
10.     Red     Skirt    Sizes :- 1
11.     Red     Socks    Sizes :- 1, 2, 3``````
Regards Mick

This is the result that the code Produces.
Code:
``````[COLOR=RoyalBlue][B]Row No [/B][/COLOR] [COLOR=RoyalBlue][B]Col(F) [/B][/COLOR] [COLOR=RoyalBlue][B]Col(G)  [/B][/COLOR] [COLOR=RoyalBlue][B]Col(H)           [/B][/COLOR]
1.      Colour  Type     Sizes
2.      Green   Dress    Sizes :- 2
3.      Green   Jumper   Sizes :- 2, 1
4.      Green   Pants    Sizes :- 1
5.      Green   Skirt    Sizes :- 2, 1
6.      Green   T-shirt  Sizes :- 1, 2
7.      Red     Jumper   Sizes :- 1, 2
8.      Red     Pants    Sizes :- 1, 2
9.      Red     Shorts   Sizes :- 2
10.     Red     Skirt    Sizes :- 1
11.     Red     Socks    Sizes :- 1, 2, 3``````
Regards Mick

Hi Mick,

I got that part. But the macro shows what sizes there are for each colour and article. I need to know how many types of articles are red and not green, how many are green and not red and how many green articles have both sizes and how many red articles have both sizes. the macro lists all articles and their sizes though. it doesn't actually count anything. Thsi is what I need:

 Green only 2 (Dress,T-shirt) Red only 2 (Shorts,Socks) Red 2 sizes 1 (Socks) Green 2 sizes 1 (T-shirt) Thanks, Alex

<tbody>
</tbody>

only t-shirt is the one that is green without being red. both jumper and pants are green but there are also red jumpers and pants on the list

did you mean this which is easily changed by using filters

 Count of Article Sold Article Colour Size Total Feb-15 Jumper Green 1 1 2 1 Green Total 2 Jumper Total 2 T-shirt Green 1 1 2 1 Green Total 2 T-shirt Total 2 Feb-15 Total 4 Apr-15 Dress Green 2 1 Green Total 1 Dress Total 1 Skirt Green 1 1 Green Total 1 Skirt Total 1 Apr-15 Total 2 May-15 Pants Green 1 1 Green Total 1 Pants Total 1 Skirt Green 2 1 Green Total 1 Skirt Total 1 May-15 Total 2 Grand Total 8

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Replies
6
Views
378
Replies
0
Views
206
Replies
13
Views
704
Replies
1
Views
372
Replies
1
Views
303

1,196,368
Messages
6,014,859
Members
441,850
Latest member
peh16

### 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.

### Which adblocker are you using?

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

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