Using count function for multiple columns

S

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 ??
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula for success... corny I know 4
IF Lookup Formula 2
Cell Color Recognition and Summing of Data Within a Specified Colo 1
COUNTIF: criteria is a format 4
How can I count the number of cells with a particular color/shade 2
Can you change a format WITHIN a formula? 1
Sum up results from If statement 2
How do I lookup from a table 3

Top