Count Number of Unique Cells That Don't Have Certain Characters

L

limh5

Hi,

I'm trying to count the number of unique cells that don't have a "#" o
"-" inside.

I have a column of cells that contain names. Some of these names ar
repeated, and some have characters such as "-" and/or "#" - I'm tryin
to exclude these cells from being counted.

I have 2 formulas that each does half of what I want, but I need t
combine the 2 formulas to get the right answer:

This formula counts the number of unique cells (and takes care o
blanks): =SUM(IF(COUNTIF(C4:C3689,C4:C3689)=0, ""
1/COUNTIF(C4:C3689,C4:C3689)))

This formula counts the number of cells that don't have a "#" or "-":
=SUMPRODUCT(N(LEN(SUBSTITUTE(SUBSTITUTE(C4:C3689,"-",""),"#",""))=LEN(C4:C3689))


The two formulas individually aren't very useful. Do you know how t
combine the 2 formulas?

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top