counting unique values

[mbernal]

Hello - i am trying to write an equation that counts the number of unique values with a criteria associated with it. Below is an example data set, what I want to count is a number of unique animals (column A) but based on a criteria (column B). Using the below I would want an equation that counted the number of unique animals associated with Ed
Ed = 3 animals

Animal Person
Dog Ed
Cat Rob
Frog Jan
Monkey James
Dog Ed
Dog Rob
Cat Ed
Elephant Ed

Thanks!

 

[AhoyNC]

This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Animal	Person	Person =	Ed
2	Dog	Ed	Count	3
3	Cat	Rob
4	Frog	Jan
5	Monkey	James
6	Dog	Ed
7	Dog	Rob
8	Cat	Ed
9	Elephant	Ed
Sheet

 

[mbernal]

thnak you! that worked perfectly! Is it possible to add a second criteria to it?

 

[AhoyNC]

You're welcome.
You could add a 2nd criteria. How about an example of what you want to add.

 

[mbernal]

if we use the origional example but add a criteria in column C that says a Y or N. Then I would want the unique count of animals that is attributed to Ed and with a Y

Animal	Person	Sent	Person =	Ed
Dog	Ed	Y	Count	2
Cat	Rob	Y
Frog	Jan	Y
Monkey	James	N
Dog	Ed	N
Dog	Rob	N
Cat	Ed	N
Elephant	Ed	Y

<tbody>
</tbody><colgroup><col><col span="2"><col><col></colgroup>

 

[AhoyNC]

Enter with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Animal	Person	Sent	Person =	Ed
2	Dog	Ed	Y	Sent =	Y
3	Cat	Rob	Y	Count	2
4	Frog	Jan	Y
5	Monkey	James	N
6	Dog	Ed	N
7	Dog	Rob	N
8	Cat	Ed	N
9	Elephant	Ed	Y
Sheet

 

[mbernal]

thanks for your help!

 

[mbernal]

thank you! worked like a charm