Count unique rows and columns

[George]

Hi -

I have a matrix (c7:i311) that contains names. For
example, a cell may contain "boat", while in the same row
(or other rows), a merged (2 or more) cell may also
contain "boat". I need to be able to perform a count (a
single number) of all unique names (many "boat"s = 1
occurance) across the entire table. I have tried

"=SUM(IF(FREQUENCY(IF(LEN(C7:C311)>0,MATCH
(C7:C311,C7:C311,0),""),IF(LEN(C7:C311)>0,MATCH
(C7:C311,C7:C311,0),""))>0,1))"

which gives a unique count for each column, but not a
unique count for all coumns together. Is there a way to
make a "single" pass over all cells (single and merged) in
the entire matrix?

Thank you.
George

 

[hgrove]

George wrote...

I have a matrix (c7:i311) that contains names. For example, a
cell may contain "boat", while in the same row (or other rows),
a merged (2 or more) cell may also contain "boat". I need to be
able to perform a count (a single number) of all unique names
(many "boat"s = 1 occurance) across the entire table. . . .

...

Merged cells may screw this up (though it seems to work under XL97)
but try

=SUMPRODUCT((C7:I311<>"")/(COUNTIF(C7:I311,C7:I311)
+(C7:I311="")))

However, if you mean, for example, that if D77:G77 were berged an
contained "boat", that this should be treated as an instance of "boat
in each of these columns (D/E/F/G), then you'd need VBA because merge
cells just don't work that way

 

[Guest]

HGROVE:

Works perfectly. Thank you very much. (Your solution is
about a third as complex as mine, which didn't work.)