Counting Unique entries in a list eliminating Blanks and Merged Cells

[chigins]

Here is what I am trying to do. I have a list of room #s. I want t
count all the unique room #'s in a single column. The column contain
bland cells and Merged cells with Title information as the ID
location or switch chaged down the worksheet. I would like to coun
only the unique room #'s eliminating all the the blank cells and all o
the merged cells (the merged cells never contain room # information)

I am sure this can be done and I can do it effectively when I delet
the merged cells but I would like to eliminate that step. Thank you

Charle

 

[Harlan Grove]

Here is what I am trying to do. I have a list of room #s. I want to
count all the unique room #'s in a single column. The column contains
bland cells and Merged cells with Title information as the IDF
location or switch chaged down the worksheet. I would like to count
only the unique room #'s eliminating all the the blank cells and all of
the merged cells (the merged cells never contain room # information)

I am sure this can be done and I can do it effectively when I delete
the merged cells but I would like to eliminate that step. Thank you

Eliminating blanks isn't difficult.

=SUMPRODUCT((Range<>"")/(COUNTIF(Range,Range)+(Range="")))

Eliminating merged cells is much, much harder. It's a very good idea never
to use merged cells in any range that would ever be used in any
calculations. It'd require VBA to do this. Do you want a VBA solution?