Excel: Count unique text entries with multiple criteria

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
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

This question generated 11 answers. To proceed to the answers, click here.

This thread is current as of November 14, 2014.

For more resources for Microsoft Excel