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".

[results]	A	B
1	description	test
2	Tiny Shoes	NEED FORMULA HERE; 1
3	Small Shoes	2
4	Tiny Socks	0
5	Small Socks	0
6	Tiny Shirt	1
7	Small Shirt	0

<tbody>
</tbody>

[raw data]	A	B	C	D
1	ID	Color	description
2	aaa	red	Tiny Shoes
3	aaa	red	Tiny Shoes
4	aaa	blue	Tiny Shoes
5	aaa	red	Small Shoes
6	aaa	red	Tiny Shirt
7	bbb	blue	Tiny Shirt
8	bbb	blue	Tiny Shirt
9	bbb	red	Small Shoes

<tbody>
</tbody>

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.