May 14 2022 01:56 PM
Hi there,
I have a list of people as columns and list of bird species as rows, when each person locates a bird species they put an X under their column name aligning with the corresponding row for that species. If someone has a bird that nobody else has it is unique and I have a formula that identifies these unique records and colours them red which is great. I'd like to add a row that sums the number of these unique records to show how many unique species each person has located. In the attached shot you can see the unique records in red- is there a simple way/formula to include a row that sums these unique observations for each person? Similar to the total species observed, but a sum of unique species. Thanks for any help!
May 14 2022 02:41 PM
Let's say the observations are in columns B to AK.
In the next available column AL, enter the following formula in row 6:
=COUNTIF(B6:AK6,"x")
Fill down to the last used row. Let's say that this is row 100.
The number of unique observations is
=COUNTIF(AL6:AL100,1)
May 14 2022 02:48 PM
May 14 2022 02:55 PM
@Hans Vogelaar thanks for your reply,
Unfortunately, that didn't seem to work. I've captured another screenshot here showing the row/columns, the last row of data is 829.
May 14 2022 02:59 PM
The last person is in column AB, so the auxiliary formula in row 6 should be =COUNTIF(B6:AB6,"x")
Let's say that you enter this formula in AF6, then fill down to AF829.
The final formula then becomes =COUNTIF(AF6:AF829,1)
May 14 2022 04:15 PM
Another 365 solution with some difference of style
= LET(
Sumλ, LAMBDA(x,SUM(x)),
obs, SIGN(observed="x"),
unique?, BYROW(obs,Sumλ)=1,
uniqueObs, FILTER(obs,unique?),
countUniq, BYCOL(uniqueObs, Sumλ),
countUniq
)
May 14 2022 04:17 PM
May 14 2022 04:19 PM
May 14 2022 04:34 PM
May 14 2022 05:33 PM
@JMB17 Hallelujah that worked, thanks Hans and JMB17! That last formula looks complex but it does what I want, thanks for the help!
May 14 2022 05:56 PM
May 15 2022 03:08 AM
I fully understand the difficulty. The code only works with Excel 365 or 2021 and looks completely alien.
For any using 365, I attach a workbook illustrating the formulae.
ps I have also included a simpler version of the formula that uses a helper range.