Produce the count value next to each filterable item in a spreadsheet

HI,

pls try and fill down

=IF(A1<>A2,COUNTIF(A:A,A1),"")

Select allOpen in new window

Regards

If I paste that in A2, I get a circular reference.

Macro code that could be used in multiple sheets would be better.

the formula should be in col C

Ok, if I put that formula in C1 and copy down, I do get the values in a cell but what I really need is for it to show for any item in any filter that is applied to any column automatically. I think a macro is better for this.

Could you send a dummy?

Book1.xlsx

How should it be done if more than one column?

The original screenshot shows that I am trying to create a value at the end of the filtered item itself.  I don't know how to do it.  Perhaps start by creating a filter row so that you can see how that process works before trying to create the macro to provide more info in the filter.

AFAIK The filter menu cannot be changed

Format the data as a Table and add a Total Row that uses Count
Excel Tables
Book1--2-.xlsx

The total row will not give me a count of like items in the filter unless they are all alike in the column.

They are the same in the example

Needs to appear inside the filter menu, next to each item.

You cannot do that, as already stated by Rgonzo.

Another work around might be a PivotTable
Book1.xlsx

I have seen it done in the past but not enough info to go on.

This is what you want?
Please see attached...
Count-Next-To-Filter.xlsx

No, the number needs to appear in the filter list.

This is best we can do;
Count-Next-To-Filter_v2.xlsx

I'm wanting to produce the count inside the first column.  As an alternative, would it be possible to click a column that I need counts for, and then run a macro against it?  This would at least eliminate the need to do a fill down.

You can get exactly what you want, but will need to use an auxiliary column for the filtering.

I put the following formula in cell B2 and copied it down:
=A2 & "-" & COUNTIF(A:A,A2)

I then filtered that column instead of the original. The AutoFilter dropdown shows the original text with an appended dash and count: Apples-1, Oranges-7, Bananas-13. You can then hide the original column if you like.
AutoFilterWithCountQ29023622.xlsx

Let me know if this is something you can work with.
To display the count of items, select the cell A1 and to hide the count of items select any other cell on the sheet.
CountItems.xlsm

Neeraj,  Thank you!

That is perfect.  That I can work with.  How would I get that to work regardless of which column is selected?

Very nice work.

I added a Worksheet_SelectionChange sub to the worksheet code pane. When you select a header label with an AutoFilter, it moves the selection to an auxiliary column and displays the count of items in the dropdown dialog. The count does not include cells that are hidden by filters. As written, column B is the auxiliary column.

The sample workbook includes three autofilter columns plus the auxiliary one. You can select any of the three, and the dropdown will show the counts.

Dim MasterCol As Range, TargCol As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, rg As Range, targ As Range, TargColumns As Range, targFilter As Range
Dim addr1 As String, addr2 As String, frmla As String
Set MasterCol = Range("B1") 'Header label for auxiliary column filter

Set TargColumns = Me.AutoFilter.Range.Rows(1)
Set targ = Intersect(Target, TargColumns)
If Not targ Is Nothing Then
    Set TargCol = targ.Cells(1)
    If TargCol.Address <> MasterCol.Address Then
        Set rg = Intersect(MasterCol.EntireColumn, Me.AutoFilter.Range)
        Set rg = rg.Offset(1, 0).Resize(rg.Rows.Count - 1, 1)
        Set targFilter = Intersect(rg.EntireRow, TargCol.EntireColumn)
        addr1 = TargCol.Offset(1, 0).Address(True, True, xlR1C1)
        addr2 = targFilter.Address(True, True, xlR1C1)
        frmla = "SUMPRODUCT(SUBTOTAL(3,OFFSET(" & addr1 & ",ROW(" & addr2 & ")-ROW(" & addr1 & "),0))*(" & addr2 & "=RC" & targ.Column & "))"
        
        Application.EnableEvents = False
        For Each cel In rg.Cells
            cel.FormulaR1C1 = "=RC" & TargCol.Column & "&"" - ""&" & frmla
        Next
        MasterCol.Select
    Application.EnableEvents = True
    End If
End If
End Sub

Select allOpen in new window

AutoFilterWithCountQ29023622.xlsm

You're welcome frugalmule! Glad you found it useful.

The proposed solution depends upon the Selection Change Event and a ListBox1 which is sitting on the sheet.
The listbox1 becomes visible only when you select the cell A1 else it becomes invisible.

How would I get that to work regardless of which column is selected?

I am unsure of your requirement here.
Do you have more than one columns which you want to have the same functionality with?
OR do you want the filtered count list to appear if you select any cell in Row1, not only A1?
Can you elaborate it to help me to understand it?

Also is your actual workbook different from one you posted as a sample workbook?

Thank you again. Yes I would want the results to show up if I click on the top of any column period it currently shows at A1 and should also show at B1, C1, D1, E1, etc. As long as those columns have data. Yes, the intent is that my actual spreadsheet could have virtually any data.

Perfect.  I posted a follow-up https://www.experts-exchange.com/questions/29025736/Make-it-possible-to-call-macro-from-any-computer-or-other-sheet.html regarding running the macro from other sheets.

Thanks for the feedback. Glad it worked as desired.

Neeraj, when I try to copy your macro into another macro enabled sheet (saved as xlsb), I do not see the macro.

Where are you copying the macro? You need to copy it on the Sheet Module.