Counting unique instances of data in 2d range

[Rodney Baker]

Hi. I have a roster that I'm working on that has 9 columns (Names) x 6
Rows (dates). I need to count the number of rows that each name appears
in, given that each name may appear more than once in a given row. I don't
want to count duplicate instances of a name in a given row.

I have named the relevant range "Roster". On sheet 2 of the workbook I
have a list of the names of the people involved in range A1:A13. In B1:B13
I tried =Countif('Sheet1'!Roster, A1) (with the formula copied down to
B13) which gives a count of all instances of the relevant name in the
named range. How do I refine this to only count the first instance of a
name in each row (and thus a count of how many rows, between 0 and 6) each
name appears in?

Thanks,

 

[Rodney Baker]

Hi. I have a roster that I'm working on that has 9 columns (Names) x 6
Rows (dates). I need to count the number of rows that each name appears
in, given that each name may appear more than once in a given row. I
don't want to count duplicate instances of a name in a given row.

I have named the relevant range "Roster". On sheet 2 of the workbook I
have a list of the names of the people involved in range A1:A13. In
B1:B13 I tried =Countif('Sheet1'!Roster, A1) (with the formula copied
down to B13) which gives a count of all instances of the relevant name
in the named range. How do I refine this to only count the first
instance of a name in each row (and thus a count of how many rows,
between 0 and 6) each name appears in?

Thanks,

I realised after I posted that what I'm looking for is basically a count
of distinct entries in each row (analogous to sql SELECT DISTINCT Name
from ...). Anyway, I've solved it for myself, but I'm sure there's a
better way than this. What I've done is to name each row in the relevant
range as a separate range Row1 to Row6. The following formula does what I
want but, like I said, there must be a more elegant way (especially since
I may want to expand the number of rows in the spreadsheet later on...).

=SUM(IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row1,0)),0,1),IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row2,0)),0,1),IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row3,0)),0,1),IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row4,0)),0,1),IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row5,0)),0,1),IF(ISNA(MATCH(A2,'Roster 01-Aug - 09-Sept
2004.xls'!Row6,0)),0,1))

Any suggestions?

 

[Jason Morin]

Assuming "roster" is found on Sheet1, try:

=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet1!"&ROW(roster)
&":"&ROW(roster)),A1)>0))

in B1 of Sheet2 and fill down.

HTH
Jason
Atlanta, GA

 

[Rodney Baker]

Assuming "roster" is found on Sheet1, try:

=SUMPRODUCT(--(COUNTIF(INDIRECT("Sheet1!"&ROW(roster)
&":"&ROW(roster)),A1)>0))

in B1 of Sheet2 and fill down.

Thanks heaps, Jason - works great! I knew there had to be a more elegant
way. I tried sumproduct earlier but couldn't get the syntax right. What
does the '--' do again?

 

[Jason Morin]

The "--" converts the boolean values (TRUE/FALSE) to
numbers so that they can be summed. First it converts
them all to 0 or -1, and then the second "-" converts
the -1 to 1 and the 0 stay 0.

Jason