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

[TABLE="width: 304"]
<tbody>[TR]
[TD]Animal[/TD]
[TD]Person[/TD]
[TD]Sent[/TD]
[TD]Person =[/TD]
[TD]Ed[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Ed[/TD]
[TD]Y[/TD]
[TD]Count[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Rob[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Frog[/TD]
[TD]Jan[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Monkey[/TD]
[TD]James[/TD]
[TD]N[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Ed[/TD]
[TD]N[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]Rob[/TD]
[TD]N[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cat[/TD]
[TD]Ed[/TD]
[TD]N[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Elephant[/TD]
[TD]Ed[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col><col></colgroup>[/TABLE]

 

[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