accumulated presence/absence (richness) over 3 times

[Broeckie]

Hi,

I want to calculate the species richness for 40 sites (sum of presence absence per row). This would be easy with a simple countif-function (where only cells with a value >0 are summed), but I have this data for each site (row) 3 times (WET1, WET2, WET3). How can I calculate the total richness (combined for all three times)? It should be that if a species was already encountered in a previous time point, it is not added again in the total sum.
In attach I added a species/site matrix, with 40 sites, but each sampled three times. Which formula can be used for this?

many thanks

[Glenn Kennedy]

Does this mean that for each species at each site, the answer will either be 0 or 1?

[Broeckie]

yes, indeed, the values now are abundances. But each cell > 0, is a 1, the others 0

[JohnTopley]

Will this give the desired result ..

=MAX(COUNTIFS($A$2:$A$121,"WET1",C$2:C$121,"<>0"),COUNTIFS($A$2:$A$121,"WET2",C$2:C$121,"<>0"),COUNTIFS($A$2:$A$121,"WET3",C$2:C$121,"<>0"))

I added a helper column A for the site using =Right(B2,4))

[Broeckie]

It's in the right direction, but I need this for each row, now it is per column. Thanks in advance!

[Glenn Kennedy]

Here's one way. There will be others/better ways...

[Broeckie]

Thank you! This is exactly what I meant!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.