Sum of records unique to row, by column

[VennZenn]

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! 

[#2]

@VennZenn 

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)

[#3]

If you have lambda function, you could try (say the data containing "x" for species spotted is in B5:Z30):

=BYCOL((B5:Z30<>"")*(BYROW(B5:Z30, LAMBDA(rw, COUNTA(rw)))=1), LAMBDA(col, SUM(col)))

[#4]

@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. 

[#5]

@VennZenn 

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)

[#6]

@VennZenn 

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
  )

[#7]

I think I see what we're trying to do. The first part makes sense, and I now have 1s in each row where unique observations are, showing in column AF. When I enter the second formula is counts the 1s and displays in the cell containing the formula... There are 57 total 1s, but they need to be assigned to be split into each person. It's close but not quite there...

[#8]

Thanks Peter,
I'm not familiar with lambda, not sure where to enter this type of thing, it might be above my pay grade...

[#9]

If you don't have lambda's or not comfortable with using them, then you could use Hans's formula in column AF and then try this in column B (either above or below your table) and then copy across.

=sumproduct(--(B6:B829="x"), --($AF6:$AF829=1))

[#10]

@JMB17 Hallelujah that worked, thanks Hans and JMB17! That last formula looks complex but it does what I want, thanks for the help!

[#11]

I probably should have suggested countifs, which would likely be more familiar. But, either one should work for you, I think.

=countifs(B6:B829, "X", $AF6:$AF829, 1)

[#12]

@VennZenn 

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.