Sorting multiple items by multiple criteria

Originally Posted by sflearn

this is a situation where I think Excel can explain that IF Xn has Yn that has size values show up twice or more, it will show me a list of just those items.

I am working with data on clothing, it has item, style, description, attribute (color), size.

I wrote it out in terms... perhaps it makes sense.

Xn- refers to all possible style numbers, there are probably more than 100 in a given sheet. xn+1, xn+2, and so on.

Yn- refers to all possible colors within an Xn group of items. (there are many items within a style due to size and color)

I wrote it out like this:

Within Xn, and within Yn, find text values= XS, S, M, L, XL

If condition exists where 2 or more of these situations occur, count them and show them.

If I were to create such a function, I would call it my '2 or more' function.

Hi,

assuming A B and C are the columns, and the rows are 1:200 then in row 1

=SUMPRODUCT(--(A1:A$200=A1)*(--(B1:B$200=B1)*(--(C1:C$200=C1))))

and formula fill that down to the end, anything not = 1 is a double

Perhaps

=IF(SUMPRODUCT(--(A1:A$200=A1)*(--(B1:B$200=B1)*(--(C1:C$200=C1))))>1,"Doubled","")

will more suit your needs. (adjust columns and row numbers to your data)

=IF(SUMPRODUCT(--(A$1:A$200=A1)*(--(B$1:B$200=B1)*(--(C$1:C$200=C1))))>1,"Doubled","")

will show both parts.

hth
---