Count Discrete Text values in column if other column value

[chauber]

I have a large set of data from which I am trying to generate information. I am having difficulty with the following example (obviously my data set is much larger than this):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Favorite Color
[/TD]
[TD]# of Pets
[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]yellow[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]green[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]David[/TD]
[TD]green[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Richard[/TD]
[TD]orange[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Vicki[/TD]
[TD]blue[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

What I am trying to do is make a function that will determine how many different favorite colors there are for people who have X amount of pets. For example, there are 2 different favorite colors for people who own 3 pets.

Since my data set is large, I would like to create a formula that can do this over a ~5000 rows.

Thanks

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try


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

A​

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

B​

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

C​

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

D​

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

E​

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

F​

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

1​

[/td][td]

Name​

[/td][td]

Favorite Color​

[/td][td]

# of Pets​

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

# of Pets​

[/td][td]

Diff Colors​

[/td][/tr]

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

2​

[/td][td]

Joe​

[/td][td]

yellow​

[/td][td]

4​

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

3​

[/td][td]

2​

[/td][/tr]

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

3​

[/td][td]

Kelly​

[/td][td]

green​

[/td][td]

3​

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

4​

[/td][td]

1​

[/td][/tr]

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

4​

[/td][td]

David​

[/td][td]

green​

[/td][td]

3​

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

2​

[/td][td]

1​

[/td][/tr]

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

5​

[/td][td]

Richard​

[/td][td]

orange​

[/td][td]

2​

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

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

6​

[/td][td]

Vicki​

[/td][td]

blue​

[/td][td]

3​

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

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

7​

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

Criteria in colunm E

Array formula in F2 copied down
=SUM(IF(FREQUENCY(IF(C$2:C$5000=E2,IF(B$2:B$5000<>"",MATCH(B$2:B$5000,B$2:B$5000,0))),ROW(B$2:B$5000)-ROW(B$2)+1),1))
confirmed with Ctrl+Shift+Enter simultaneously, not just Enter

Hope this helps

M.