Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values and subtracting those of no interest
I have a column of client names with some that I don't want to count.
I am able to count the number of unique clients. Is there a way to exclude the names I don't want from the count of the total clients? I need to wild card the names I don't want as they are entered in multiple ways. So essentially I want a formula that will answer the question: How many unique clients do I have excluding any with 'ABC' in their name? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting unique values and subtracting those of no interest
=COUNT(1/IF(NOT(ISNUMBER(FIND("ABC",A2:A100))),MATCH($A$2:$ A$10,$A$2:$A$10,0)=ROW($A$2:$A$10)-ROW($A$2)+1))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) wrote in message ups.com... I have a column of client names with some that I don't want to count. I am able to count the number of unique clients. Is there a way to exclude the names I don't want from the count of the total clients? I need to wild card the names I don't want as they are entered in multiple ways. So essentially I want a formula that will answer the question: How many unique clients do I have excluding any with 'ABC' in their name? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values | Excel Discussion (Misc queries) | |||
Counting Unique Values | Excel Discussion (Misc queries) | |||
Counting Unique Values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions | |||
Counting Unique Values | Excel Worksheet Functions |