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 |
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 |
All times are GMT +1. The time now is 09:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com