Using count function for multiple columns

[Sekhri]

I have a spreadsheet with style numbers and colors sold over a period. for eg
A B
DN012 red
DJ111 White
TS442 BLue
DN012 Black
DR123 red
DJ111 Blue
Now i want to count the number of colors for each style. as in for DN012
there are two colors sold. How can i do it ??

 

[Mike H]

Hi,

Try this with your lookup value (DN012) in C1

=COUNT(1/FREQUENCY(IF($A$1:$A$10=C1,IF($B$1:$B$10<>"",MATCH($B$1:$B$10,$B$1:$B$10,0))),ROW($B$1:$B$10)-ROW($B$1)+1))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike