Count unique text entries with multiple criteria

[meowerson]

I am trying to count unique text 'Species' entires in column 'C' on a separate Worksheet (within the same workbook) with multiple criteria. The data are organized as follows (in 'Worksheet'):

Row#	A	B	C
1	Survey	Site	Species
2	1	a	x
3	1	b	y
4	1	b	y
5	2	a	x
6	2	a	x
7	2	b	z
8	3	a	z
9	3	b	x

<tbody>
</tbody>

I have attempted to calculate the number of unique 'Species' during survey '1' at site 'a' using the following formula:

=SUM(IF('Worksheet'!$B$2:$B$9="a")*('Worksheet'!$A$2:$A$9="1", 1/(COUNTIFS('Worksheet'!$B$2:$B$9, "a", 'Worksheet'!$C$2:$C$9, 'Worksheet'!$C$2:$C$9, 'Worksheet'!$C$2:$C$9, "1"))), 0) but it says there is an error in my formula. Please help! Thanks, meowerson

<colgroup><col width="75" style="width:75pt"></colgroup><tbody>
<!--EndFragment-->
</tbody>

 

[Aladin Akyurek]

Hi Aladin,

Thanks again, but it's still not working. I get a #VALUE! message. Note that I am referencing cells on a different worksheet and am putting the reference to that worksheet (written as: 'PC Breeders'!) before every range in the formula. Would this affect your suggested formula in any way?

Sheet name should not cause a problem. Just add them ot therelevant ranges.

Here is the set up: https://dl.dropboxusercontent.com/u/65698317/meowerson%20multiconditional%20unique%20count.xlsx

 

[meowerson]

Thanks, István. I will try your method if the others don't work.

Thank again, Aladin. I am on a Mac and ^+shift+return does not work. If you have any other thoughts please let me know.

 

[Aladin Akyurek]

Thanks, István. I will try your method if the others don't work.

Thank again, Aladin. I am on a Mac and ^+shift+return does not work. If you have any other thoughts please let me know.

Control+shift+enter (Windows Excel) === Command + Return (Mac Excel)