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

Hi,

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

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

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

This formula counts the number of unique cells (and takes care of 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 to combine the 2 formulas?

Thanks.

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

On Tuesday, 21 January 2014 12:09:07 UTC+11, limh5 wrote:
Hi,



I'm trying to count the number of unique cells that don't have a "#" or

"-" inside.



I have a column of cells that contain names. Some of these names are

repeated, and some have characters such as "-" and/or "#" - I'm trying

to exclude these cells from being counted.



I have 2 formulas that each does half of what I want, but I need to

combine the 2 formulas to get the right answer:



This formula counts the number of unique cells (and takes care of

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 to

combine the 2 formulas?



Thanks.









--

limh5

One way to do this is to first create a 'helper' column somewhere - say in the neighbouring cells (D4:D3689) - that replaces all repeat occurrences of a name in C4:C3689 by the symbol # but leaves all the first occurrences of a name unchanged. You can do this by putting the array formula
= IF(MATCH($C$4:$C$3689,$C$4:$C$3689,0)=(ROW($C$4:$C $3689)-3),$C$4:$C$3689,"#")
into $D$4:$D$3689.
(Note that you subtract 3 on the right-hand side because your range starts in row 4. Otherwise you'd subtract the row number of the first entry minus 1.)

Now you can use your second formula to count the number of names in $D$4:$D$3689 that don't contain either # or - . You could combine the two formulas into one megaformula, but it looks horrendous.

Hope that makes sense.

Howard