Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count number of characters in merged cells + adjusting rowheight | Excel Programming | |||
Count the number of unique records | Excel Worksheet Functions | |||
Count Number of Characters in a cell? | Excel Discussion (Misc queries) | |||
Count number of Unique values | Excel Worksheet Functions | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |