Distinct Count Based on Multiple Criteria

[kparadise]

Hello,

I am building a tab [results]; on this tab, I have a column [column A] which lists a unique value (description). In [column B]; I need to create a formula. This formula needs to look into the [raw data] tab, and count the number of distinct IDs which share the same (description) and also have one more criteria with color = "red".

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][results]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]description
[/TD]
[TD="align: center"]test
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]Tiny Shoes
[/TD]
[TD="align: center"]NEED FORMULA HERE; 1
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Small Shoes
[/TD]
[TD="align: center"]2
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]Tiny Socks
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]Small Socks
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Tiny Shirt
[/TD]
[TD="align: center"]1
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Small Shirt
[/TD]
[TD="align: center"]0
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][raw data]
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]ID
[/TD]
[TD="align: center"]Color
[/TD]
[TD="align: center"]description
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]red
[/TD]
[TD="align: center"]Tiny Shoes
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]red
[/TD]
[TD="align: center"]Tiny Shoes
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]blue
[/TD]
[TD="align: center"]Tiny Shoes
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]red
[/TD]
[TD="align: center"]Small Shoes
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]aaa
[/TD]
[TD="align: center"]red
[/TD]
[TD="align: center"]Tiny Shirt
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]blue
[/TD]
[TD="align: center"]Tiny Shirt
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]blue
[/TD]
[TD="align: center"]Tiny Shirt
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]bbb
[/TD]
[TD="align: center"]red
[/TD]
[TD="align: center"]Small Shoes
[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

So basically, I want to look up [results].A2 in [raw data].C:C, make sure we are only looking at the "red" values in [raw data].B:B; and then count the number of distinct IDs associated with those two criteria.

 

[kparadise]

There were errors in my description column on my raw data tab! nice call!

 

[Marcelo Branco]

There were errors in my description column on my raw data tab! nice call!

Ok. See if this works

=SUM(IF(FREQUENCY(IFERROR(IF(combinedCCB2018!G$2:G$10=A2,IF(combinedCCB2018!C$2:C$10="Testing", MATCH(combinedCCB2018!B$2:B$10,combinedCCB2018!B$2:B$10,0))),FALSE),ROW(combinedCCB2018!B$2:B$10)-ROW(combinedCCB2018!B$2)+1),1))
Ctrl+Shift+Enter

M.