Q: How do I count unique entries across multiple columns?

Hi momogabi,

Here is an approach that uses an additional column.

COUNTA returns the number of its arguments that are not empty.

I put the count of unique students in the footer. it could be anywhere.

If you decide checkboxes are easier than "Y" Use COUNTIF() for both functions.

quinn

Here's another way that uses a "Counter" column that you can then use to generate a list of people that attended at least once:

The formula in P2, filled down, is:

  =IF(COUNTIF(I2:O2,"Y")>0,MAX(P$1:P1)+1,"")

This increments a counter by one whenever a name with at least one Y is encountered.

The formula in A1 of the 'Attended at Least Once' table, filled down, is:

   =IFERROR(INDEX(Table 1::C,MATCH(ROW(),Table 1::P,0)),"")

This takes the row number where the formula appears and looks it up in column P of the first table.  The row number it finds is then fed to the INDEX function, which retrieves the value (name) that appears in column C.  The IFERROR suppresses the red warning triangle if you have added more rows than the first table has.

The formula in the last cell of the 'Attended at Least Once' table, is:

     =COUNTIF(A,"<>"&"")

This simply counts cells that are not displaying a blank.

Note that the 'Name' column contains distinct names (i.e. you can't have more than one exactly the same there). Usually a first initial and last name will be enough to distinguish the names.

In Numbers it's a good idea to have compact tables that do not include a lot of blank rows as in your example.  Recommend having a look at the templates at File > New in your menu for ideas about how Numbers tables should look.  You'll get better results if you pay attention to the table-centric design of Numbers and avoid the great sea of cells on a sheet as you often find in Excel.

SG

Can one assume that each row is a unique individual?  If so, a "sumif" function can be used across each row.  Where the cells in the sumif column is >0 the person attended.