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.

 

[Marcelo Branco]

Try

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

description​

[/td][td]

test​

[/td][td]

Criteria​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Tiny Shoes​

[/td][td]

1​

[/td][td]

red​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Small Shoes​

[/td][td]

2​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Tiny Socks​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Small Socks​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Tiny Shirt​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Small Shirt​

[/td][td]

0​

[/td][td][/td][/tr]
[/table]

Array formula in B2 copied down
=SUM(IF(FREQUENCY(IF('RAW DATA'!C$2:C$100=A2,IF('RAW DATA'!B$2:B$100=C$2,MATCH('RAW DATA'!A$2:A$100,'RAW DATA'!A$2:A$100,0))),ROW('RAW DATA'!A$2:A$100)-ROW('RAW DATA'!A$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[kparadise]

I am getting a #NAME ? error.

 

[Marcelo Branco]

I am getting a #NAME ? error.

It worked perfectly for me.
What Excel version are you using?

M.

 

[kparadise]

2013 = you have a new column C entered there. I do not have that. How would that change the formula if you made it "red" instead for referencing a cell?

 

[Marcelo Branco]

Change C$2 to "Red"

M.

 

[kparadise]

Yea, it is not working. Getting #N/A now; but I can view in the raw data tab the number I need it to return; by simply filtering.

{=SUM(IF(FREQUENCY(IF(combinedCCB2018!G$2:G$2423=A2,IF(combinedCCB2018!C$2:C$2423="Testing", MATCH(combinedCCB2018!B$2:B$2423,combinedCCB2018!B$2:B$2423,0))),ROW(combinedCCB2018!B$2:B$2423)-ROW(combinedCCB2018!B$2)+1),1))}

 

[Marcelo Branco]

Yea, it is not working. Getting #N/A now; but I can view in the raw data tab the number I need it to return; by simply filtering.

Worked for me
Array formula
=SUM(IF(FREQUENCY(IF('RAW DATA'!C$2:C$100=A2,IF('RAW DATA'!B$2:B$100="Red",MATCH('RAW DATA'!A$2:A$100,'RAW DATA'!A$2:A$100,0))),ROW('RAW DATA'!A$2:A$100)-ROW('RAW DATA'!A$2)+1),1))
confirmed with Ctrl+Shift+Enter

Could you show the formula you are using?

M.

 

[kparadise]

See Above.

 

[Marcelo Branco]

See Above.

Are you using exactly the formula above (post 8)?

If so, it should have worked for you, provided (check):
The sheet name is exactly RAW DATA
The data are in columns A:C beginning at row 2; headers on row 1 (like your data sample in post 1)
There are no errors in any cell

M.