Excel listing fuction

Question

Bird species     day 1         day 2       day 3     day 4
X                  x
Y                  x             x
Z                  x                         x         x 
V                                x                     x 
B                  x                         x 
D                  x             x           x
E                                                      x 
etc..

Total= 7
Total new species day 1=x
Total new species day 2=?
Total new species day 3=?

I am searching for an Excel tagline to make the following calculation with an example version of the dataset above: I have a list where unique bird species are marked for a seven day period. Each day all bird species that are seen are noted. Now I want to calculate how many new species are added to the total list every day. Say, on day one 30 species are seen and marked. On day two, 25 species are seen and marked. On day one you have 30 new species, but on day two there might be overlap. It works with COUNTIF and then substracting if the cell before your wanted cell is "blank", but when you have a dataset that includes 40 days, the code gets annoyingly long. There should be an easier setup.

How would you calculate this using Excel?

user_4851590 · Answer

with the new BYROW and LAMBDA functions(when they become available to everyone) it is quite easy:

=SUMPRODUCT(--(BYROW($B$2:$E$8,LAMBDA(a,MATCH("x",a,0)))=ROW($ZZ1)))

But without BYROW and LAMBDA, the easiest is to have a helper column with the MATCHes in that then use COUNTIF:

In F2 we put:

=MATCH("x",B2:E2,0)

Then copy it down.

Then we just do:

=COUNTIF(F:F,ROW($ZZ1))

And copy it down.

But if one really wants to not use a helper column and does not have the new dynamic array formula we need to do some more complex formula:

The first day is an easy COUNTIF:

=COUNTIF($B$2:$B$8,"x")

The others not so easy:

=SUMPRODUCT((MMULT(--(INDEX($B$2:$E$8,N(IF({1},ROW($ZZ$1:INDEX($ZZ:$ZZ,ROWS($B$2:$E$8))))),N(IF({1},TRANSPOSE(ROW($ZZ$1:INDEX($ZZ:$ZZ,ROW($ZZ1)))))))="x"),ROW($ZZ$1:INDEX($ZZ:$ZZ,ROW($ZZ1)))^0)=0)*(INDEX($B$2:$E$8,0,ROW($ZZ1)+1)="x"))

This will need to be confirmed with Ctrl-Shift-Enter instead of Enter then dragged down.

There is a lot going on, but in essence we use MMULT to create a single vertical column of 1s and 0s. 1 if anywhere in the previous columns there is an x and 0 if none. Then we find where that array is 0 and the column of the day we are looking at is x.

Excel listing fuction

1 Answers