CODEWITHSUNDEEP
excelexcel-formulacombinations
Mathemagician
Mathemagician

Reputation: 507

Finding combinations of two columns in excel, with a condition in one column

I am trying to count the combination of columns A and B, by fixing a value for column B and excluding all the duplicates.

In the exemple bellow, I would like to count all unique combinations of column A & B where B is equal to "green". The result should be 4

A     B  
one   green  
one   green  
two   green  
four  pink  
three  green
four  pink  
blue  green  
black white  
black white

Upvotes: 0

Views: 1196

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60324

If you happen to have Excel 2016+, with the new UNIQUE and FILTER functions, you can use:

=ROWS(UNIQUE(FILTER(myRng,INDEX(myRng,0,2)="green")))

enter image description here

Upvotes: 3

Kevin
Kevin

Reputation: 2631

I have had to do this before, you can accomplish this using an array formula with the frequency function.

=SUM(--(FREQUENCY(IF(B2:B10="green",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1)>0))

Note: This formula must be entered using ctrl+shift+enter

For a complete explanation of how this works please see this article:

Count unique text values

Upvotes: 2

Related Questions