Count unique values with multiple criteria from same column

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.

Sample attached

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
Red only 2
Red both sizes 1
Green both sizes 1

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, macro, 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

An example using COUNTIFS:

=COUNTIFS(Table1[Colour],"Green",Table1[Article],"Dress",Table1[Size],2) for Green Dresses, Size 2

You can similar counts for other combinations: Add or omit criteria as required.

Originally Posted by JohnTopley

An example using COUNTIFS:

=COUNTIFS(Table1[Colour],"Green",Table1[Article],"Dress",Table1[Size],2) for Green Dresses, Size 2

You can similar counts for other combinations: Add or omit criteria as required.

I would require the number of types of articles (aka clothes) that are only green but not also red. A countif like you described would not tell me how many types of clothes are green without them also being red. A good example would be t-shirt since there are no green and red t-shirts on the list just one colour. I only need a head count not anything more detailed. Sonething like =countifs (Table1[Colour],"Green",Table1[Colour],"<>"&"Red"). That of course doesn't work but it's what I am looking for. Once I have that I can also calculate how many of those come in 2 sizes and split the numbers for all the months. Does that even remotely make sense?

More examples .....pick and mix your criteria


=COUNTIFs(Table1[Colour],"green") Green only garments

=COUNTIFs(Table1[Colour],"red") red only garments

=COUNTIFs(Table1[Article],"t-shirt") ..... t_shirts any colour

Sample attached.

Originally Posted by JohnTopley

Sample attached.

Hi John,

Thank you for this but it is still not what I need. You did stop short of seeing what I would need. Attached you can see that if you also apply that formula to the other types of clothing, T-shirts and Shorts, they have 0 for 2 of the colours and over 0 for just one colour. This is what I need. I want to know how many types of articles only come in the one colour without coming in the other colours. T-shirts come only in green, there are 0 red t-shirts and 0 yellow t-shirts.shorts come only in red. there are 0 green shorts and 0 yellow shorts. Furthermore, i do not need to know how many green t-shirts there are on the list just that there is one type of clothing that is purely green and no other colour. same for shorts. So out of the 6 types of clothes (dress, jumper, pants, skirt, t-shirt and shorts), only 1 is green and no other colour, only 1 is red and no other colour and 0 are yellow without being any other colour.

The data I actually need to work with has exactly 8531 unique values and not 6. These can repeat any number of times or just have one entry. I need to know how many of the 8531 are only green and how many are only red. I can, and I actually did apply the countif formula since it was the only one that I could think of using in a way. Accounting for the sizes and month breakdown, this took me forever to do and I am still not 100% sure I have the right count. But I would like to know if there is any other way of doing it.

Alex