Solved: Count Unique Values When Filtered

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the following formula to count a list of unique values in the column range 'OutputNAme':

=COUNTA(OutputName)-COUNTIF(OutputName,"")
These values are themselves the result of the following array formula:

=IF(ROWS(B$7:B7)<=$C$3,INDEX(INDIRECT(B$6),SMALL(IF(Status=$B$3,ROW(Status)-ROW(Input!$B$7)+1),ROWS(B$7:B7))),"")

The unique value count works fine when the list is unfiltered, but if I apply a filter to another column in my data range, the 'Count' formula no longer displays the correct record count.

Despite searching the internet for a solution for most of the day, I've been unable to find a solution.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may amend my formula to work when my data range is filtered and unfiltered.

Many thanks and kind regards

[snb]

A UDF ?


function unique_snb(c00 as Range)
on error resume next
unique_snb=0

unique_snb=c00.specialcells(12).specialcells(2).count
unique_snb=c00 +c00.specialcells(12).specialcells(-4121).count
end function

[hobbiton73]

Hi @snd, thank you for taking the time to reply to my post and for putting together a solution.

Forgive me for asking, especially as you have already been so helpful, but would there be a chance please that this could be done via a formula?

Many thanks and kind regards

[snb]

It is a formula:

in cell B7
=unique_snb(outputname)

[hobbiton73]

Hi @snb, thank you very much for this and your continued help.

I've tried the UDF you kindly provided, and unfortuantely I'm unable to get this to work.

Although I've taken the spreadsheet design from here http://ms-office.wonderhowto.com/how-to/extract-part-table-from-larger-table-excel-359205/

To perhaps illustrate, better than I can explain, I've attached the spreadsheet I'm working on.

The spreadsheet I'm having difficulty with is the 'Output' sheet, in particular cell D3.

As you can see unfiltered, this cell correctly shows 3 unique records, but when filtered by the 'Role' column for example, this still shows 3 rather than 2.

I hope this helps.

Once again many thanks for all your help.

Kind regards

[snb]

No attachment ?

[hobbiton73]

Hi @snb, my apologies, please find the file attached.

Many thanks and kind regards

[SamT]

In every case, X = 5 :dunno ..... :banghead::devil2:

X = c00.SpecialCells(xlCellTypeVisible).Count
X = c00.SpecialCells(xlCellTypeBlanks).Count
X = c00.SpecialCells(xlCellTypeVisible, xlTextValues).Count
X = c00.SpecialCells(xlCellTypeVisible, xlNumbers).Count
X = c00.SpecialCells(xlCellTypeVisible, -4121).Count


BTW, what is the constant with the value -4121?

[Aflatoon]

Try:
=COUNT(1/FREQUENCY(IF(SUBTOTAL(103,OFFSET(OutputName,ROW(OutputName)-MIN(ROW(OutputName)),0,1)),IF(OutputName<>"",MATCH(OutputName,OutputName,0))),IF(OutputName<>"",MATCH(OutputName, OutputName,0))))
array-entered using Ctrl+Shift+Enter.

[Kenneth Hobs]

Have you tried =Subtotal?

[hobbiton73]

Hi @Aflatoon, thnak you for taking the time to reply to post and for the solution.

I haven't tried your solution yet, but after working through this today, I've come up with an alternative.

I went back to the original tutorial which my sheet was built around here:http://ms-office.wonderhowto.com/how-to/extract-part-table-from-larger-table-excel-359205/

Rather than having the one drop down list called 'Dep' in the tutorial, I added some addtional drop down lists so the data is filtered as part of the extract rather than having to apply Autofilter.

I will however be looking at the solution you provided because it will certainly help with another project I'm working on.

Many thanks and kind regards