formula for values on multiple lines

[smakatura]

I have 400+ rows of data and want to use a formula not a pivot table to return value. I will be using the "color value" returned value in a different formula.

data has item# on each row. any item number could be different colors (imagine a shirt with an item number 1111 that is available in blue or red).

so lets say in the last week I sold the following shirts (each line represents one shirt sold)
[TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]item #
[/TD]
[TD="class: xl65, bgcolor: transparent"]color
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1111
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[/TR]
</tbody>[/TABLE]


If I do a countif formula on column c I can see how many times an item number was sold - regardless of the color of the shirt
[TABLE="width: 204"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 144, bgcolor: transparent"]C
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]item #
[/TD]
[TD="class: xl65, bgcolor: transparent"]color
[/TD]
[TD="class: xl65, bgcolor: transparent"]count of item number
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1111
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[/TR]
</tbody>[/TABLE]


I want a formula in column D that will say if the colors sold were all one color or if they were multiple colors. so if shirt 1122 sold 2 shirts and both were red, it will return "Red". whereas shirt 1133 sold 2 but since there was a red and blue shirt sold, it will return "multiple colors". obviously if there is only one shirt sold of a number (1111), it returns the color of the shirt sold
[TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, width: 144, bgcolor: transparent"]C
[/TD]
[TD="class: xl65, width: 101, bgcolor: transparent"]D
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]item #
[/TD]
[TD="class: xl65, bgcolor: transparent"]color
[/TD]
[TD="class: xl65, bgcolor: transparent"]count of item number
[/TD]
[TD="class: xl65, bgcolor: transparent"]color value
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1111
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]1
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1122
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]red
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]multiple colors
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]1133
[/TD]
[TD="class: xl65, bgcolor: transparent"]blue
[/TD]
[TD="class: xl65, bgcolor: transparent"]2
[/TD]
[TD="class: xl65, bgcolor: transparent"]multiple colors
[/TD]
[/TR]
</tbody>[/TABLE]


I am unsure how to do the color value formula

 

[AlphaFrog]

Try this in D2 and copy down...

=IF(COUNTIFS(A:A,A2,B:B,B2)=C2,B2,"Multiple Colors")

 

[smakatura]

Perfect thanks.

I have never used the formula that way, I now have something to explore