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'):
<tbody>
</tbody>
I have attempted to calculate the number of unique 'Species' during survey '1' at site 'a' using the following formula:
<colgroup><col width="75" style="width:75pt"></colgroup><tbody>
<!--EndFragment-->
</tbody>
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>