Counta function

New Contributor

I need some guidance on a Counta or countif function for the attached file.

I need a formula for cells D111 and D112.

It is a presence/absence table, with a star in column D (D2:D106) representing the presence of a species in that vegetation group (cell D1).

More explanation in the spreadsheet itself.

3 Replies

Hi @Eren_Reid 


You can use Pivot table to count values without repeats. File is attached for reference.

I think unique values without repeat in Genus are 36 not 32.






@tauqeeracma Thank you for this

I am aware of the pivot table but it is a lot of work especially when i might have 20 something veg groups, hence i was wondering of there is a countif unique formula?




In that case, you can use supporting columns to count required values without repeats. 

You simply need to add new veg group in sheet1 and drag formula in sheet2.


Revised excel file attached.

Hope it will help