Hello!

So it's 2 years later, but I just had the same excel shannon diversity index challenge. I really needed a single cell function because I had 400 separate surveys with lots of zero counts and 400 SDIs to calculate! (so I really really wanted to be able to drag a single function down. After a couple hours of forum hunting, I managed to get this to work. It's a hybrid of things people tried here.

My input data were a little different; organized horizontally. I used a pivot table to autocalculate plot totals (used to calculate Pi), and put them in column AG.

=-SUMPRODUCT((P9:AF9/AG9),LN(P9:AF9/(AG9)+(P9:AF9=0)))


Hope this saves someone some ln(0) misery! :)