Excel COUNT function, excluding duplicate values

[knightcloud]

hi everyone,

i hope somebody could help me on this.

i have a list of data for plant species. on the first sheet labeled "list of species", it includes the family name, common name. genera and species of plants. on the second sheet "species count", i would like to count the number of genera and species belonging to a family but excluding duplicate values.

let's say:
Sheet1

-----------A---------------B----------------------C
1---FAMILY--------GENUS-----------SPECIES
2---Rosaceae-----Rosea--------------indica
3---Rosaceae-----Rosea--------------pudica
4---Rosaceae-----Prunus-------------fragrans
5---Fabaceae-----Agathis------------columnaris
.
.
n

what i would like to do in sheet 2 is to count the unique value in column based on the range of values in column a. in sheet 2, i have already removed the duplicate values in column a leaving the unique values only. so, in sheet2, it should show computation like this:

Sheet 2

---------A--------------------B-------------------C
1---FAMILY---------GENERA------SPECIES
2---Fabaceae------------1-------------------1
3---Rosaceae------------2-------------------3
.
.
n

i have attached a sample table.

any suggestion would be greatly appreciated.

[teylyn]

Hi knightcloud,

if you add a column to your data, with this formula

=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)

starting in row2 and copied down, then you can easily build a pivot table. See attached.

[DonkeyOte]

Given use of XL2007 you could replace the SUMPRODUCT with a COUNTIFS equivalent.

FWIW I agree with Teylyn - ie you could calculate without use of helpers but said calculations would be inefficient (esp. if there is a large dataset involved).