Not sure I got it right but perhaps the attached workbook contains the solution you need. I added a column that determines if the row is visible (1) or not (0).

Then I extended the COUNTA formula with FILTER.

Riny

Thank you for your quick reply.  I couldn't get the formula to work, kept getting error messages.

I uploaded another version of the worksheet, this time I have the complete table in one tab, and the other tab I have the slicers that I am testing.

There are a total of 16 unique companies.  For this I will be slicing on any one of the industry slicers, which are on the left, and then any one of the services which are on the right.

For instance, if I choose Retail I ended up with 20 total results with 11 unique companies.  If I further select Support, I end up with 16 total results and 8 unique companies.

The reason I am building the dashboard is to help customers choose which companies, and which sites they want to consider for outsourced workers.  One of the charts I will have is a bar chart that has one bar showing the total number of companies and another bar showing the total number of sites, this part of the bar chart will be static.  The third and fourth bars will show the same amount of companies and sites at the start of the presentation, but then as selections are made they will dynamically change to reflect narrowing down of choices.  I will most like have over 100 companies and 100's of sites in my data set. Once the industry and services are selected, the customer will then choose preferred geographies, to further narrow down the selection process. Hopefully the use case helps explain what I am trying to accomplish.

Does the attached file work for you? If not, which Excel version are you working with?

Yes it worked!

Thank you so much.

When I used the formula it didn't work, but then I copied your worksheet into mine and it worked then.  Not sure why it worked that way and not by just using the same formula in my existing worksheet.

I am on Version 2308 ( Build 16731.20234 Click to Run)

Hi Riny,

I am trying to do two similar actions and I again can not get the formula to work.  Yesterday I used your formula and it didn't work, but when I copied the worksheet into my master it did and continues to work.

It makes me wonder if it's the release of Excel I am on?

The first of the two actions I want to do, is count the number of continents by continent that end up in the result from slicing.  I created another column named it C, short for Continent.  In each row I assigned a number for the continents. Africa is 1, Asia is 2, Europe 3 and so on.  Then I created a table with two columns, first column lists the continents, and the second column will have the number of instances of that continent based on the result of slicing.  I used the same format as for the unique count but I replaced BPO Name with Continent and Selected with C, and for the corresponding cell in the table the appropriate number, i.e. 3 is for Europe.  I will use the table to have a tree chart to show the relative concentration of instances by continent.

=COUNTA(UNIQUE(FILTER([Continent],[C]=3)))

After putting this in the appropriate cell, I get the following error message:

Not trying to type a formula use an apostrophe....

I've also tried it without the unique in the formula.

The other action I am trying is to create a list of the cities that are in the result from slicing, no luck there as well.

I do appreciate your help.

If you can use UNIQUE it's not a version issue. The error message "not trying to type a formula ....." makes me believe that you need to use as semi-column to separate the function arguments. That's a local setting.

So try:

=COUNTA(UNIQUE(FILTER([Continent];[C]=3)))

Same error message:

There's a problem with this formula.

Not trying to type a formula?

then it goes on to explain the = and - sign and to get around it use an apostrophe first to get the text

Please share a file again. Difficult to diagnose otherwise.

The numbers in the table are just input manually at present.

My formula sits in A39 and works as intended. It counts the number of unique company names in the table as displayed. Thus, without filter it counts 16 unique company named. Filter for North America and you'll get 5.

Looking at the table you entered at the bottom, I believe you are going to need the Distinct Count option found in Power Pivot. You can create a Pivot Table / Pivot chart that uses the distinct count (rather than just count) of Company Names, for instance.

Then the formula in A39 serves no purpose as it will only return 1 number based on the rows displayed, you but can't use it to create the kind of table you added at the bottom of the work sheet.

Hi Riny,

I appreciate your response but I am not sure I understand. Am I correct that I can not have both a unique count of companies, and the count of each continents appearance after a slice is done.  My intent is to have those results drive two different charts on my dashboard. 

Would it be ok with you, if I take a screen shot of my draft version dashboard ( I think my workbook exceeds the upload limitation) and give an explanation of each chart and how I am supplying the data to it. I can also provide a screen shot after slicing to show the changes that occur.

I am traveling today, so if the answer is yes, it may not be until tomorrow before I can upload it.

In either case, thank you.

JD

Not sure what you mean by "unique count of companies, and the count of each continents appearance after a slice is done". Good idea to upload a screen shot. Anything to help me understand your needs.

Hi Riny,

I have attached a word document and 7 screen shots, to explain and demonstrate what I am trying to accomplish.  I didn't see any other way to do this.

The word document describes the process and at each stage identifies which screen shots are associated with that stage.

Thank you again for your time and efforts.

John

Hi John,

Impressive pictures and a very detailed Word doc, but it's too much detail to comprehend. Can't we focus on your initial table? From it you want to make a chart that is responsive to a slicer, counting the number of distinct customers per continent. If you can do that, you can probably do similar analyses in other areas.

Riny