Search and see only the searched data

Not sure if this is what you want, but take a look at using filters.

On the Home tab, click Sort and Filter, and then select Filter.
You will see that each column now has a small drop-down. Click the Track 1, uncheck All and check what you want to see

Hi Matt,

Change your SUM() fuctions.

To see the count of the filtered items use

Formula:

=SUBTOTAL(103,I2:I25)

To see the sum use

Formula:

=SUBTOTAL(109,S2:S25)

Do you have an updated version of your file?

OK so what would your expected outcome look like?

Hi Dirty,

Try this - it works on a double click in the non-numeric columns up to G - it will filter for any value you double click upon in those columns and if you doubleclick anywhere on the sheet it will restore the data to its unfiltered state:Dirty.xlsm

I'll have to see whether I can accommodate you tomorrow

Sorry Dirty, I need another day

Hey Dirty,

Where should I put the "Totals"??

Hi Dirty,

I interpreted "the bottom" to mean row 378 for this sample - see how you like it Dirty.xlsm

Hi Dirty,

You may want to change the code to what I have in red here:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim S, r As Long, c As Long, i As Long, j As Long, k As Long, n As Long
        Dim Totals As Range, ws As Worksheet: Set ws = ActiveSheet
        r = 2: Do Until ws.Cells(r + 1, 2) = "": r = r + 1: Loop
        c = 15: Do Until ws.Cells(1, c + 1) = "": c = c + 1: Loop
    Set Totals = ws.Range(ws.Cells(r + 2, 1), ws.Cells(r + 2, c))
                    Totals.Clear: S = Totals
                        ws.AutoFilterMode = False
            If Target.Column < 8 And Not IsNumeric(Target) Then
                k = Target.Column: S(1, k) = Target.Value
                            For i = 2 To r
            If ws.Cells(i, Target.Column) = Target.Value Then
            n = n + 1: S(1, 5) = S(1, 5) + ws.Cells(i, 5)
        For j = 10 To 17:
        If ws.Cells(i, j) <> "" Then
        S(1, j) = S(1, j) + 1: End If
        Next j
        For j = 20 To 22: S(1, j) = S(1, j) + Val(ws.Cells(i, j)): Next j
                           End If: Next i
                           S(1, 5) = S(1, 5) / n
                
    Totals = S: Totals.Interior.ColorIndex = 1: Totals.Font.ColorIndex = 2

ws.Range("A1").CurrentRegion.AutoFilter Field:=Target.Column, Criteria1:=Target.Value

End If
End Sub

So now that you have shown me, can you teach me? How could I add that code from "G" onward?

Also how can I compensate you? That's was exactly what I was looking for!

Yes that worked with one exception, I think, this is a little nit picky but how could I change the format on some cells upon double clicking? Example when I double click column T; I want to see the format changed to currency. Is this possible?

Hi Dirty,

To change the code - with the spreadsheet up hit Alt + F11 and you'll see the code in front of you - for the fix above just change the:

Totals.ClearContents

to

Totals.Clear

i.e. just delete the Contents part.

To go on from Column G will screw up the id feature wherein you can see right in the Totals bar what term was filtered, I need to know what else you want to see in order to make that possible.

To reward me, just click on the little star at the bottom left of any of my posts and award me reputation points

Hi Dirty,

Thanks for the rep and the compliment!

Replace the whole code with this - I think it's what you want - I put the filter description for H and above in the G-Slot.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim S, r As Long, c As Long, i As Long, j As Long, k As Long, n As Long
    Dim TRange As Range, Totals As Range, ws As Worksheet: Set ws = ActiveSheet
        r = 2: Do Until ws.Cells(r + 1, 2) = "": r = r + 1: Loop
        c = 15: Do Until ws.Cells(1, c + 1) = "": c = c + 1: Loop
        Set TRange = ws.Range(ws.Cells(1, 1), ws.Cells(r, c))
    Set Totals = ws.Range(ws.Cells(r + 2, 1), ws.Cells(r + 2, c))
                    Totals.Clear: S = Totals
                        ws.AutoFilterMode = False
            If Target.Column < 20 And Target.Value <> "" Then
                            k = Target.Column
        If k < 8 Then S(1, k) = Target.Value Else S(1, 7) = ws.Cells(1, k)
                            For i = 2 To r
            If ws.Cells(i, Target.Column) = Target.Value Then
            n = n + 1: S(1, 5) = S(1, 5) + ws.Cells(i, 5)
        For j = 10 To 17:
        If ws.Cells(i, j) <> "" Then
        S(1, j) = S(1, j) + 1: End If
        Next j
        For j = 20 To 22: S(1, j) = S(1, j) + Val(ws.Cells(i, j)): Next j
                           End If: Next i
                           S(1, 5) = S(1, 5) / n
                
    Totals = S: Totals.Interior.ColorIndex = 1: Totals.Font.ColorIndex = 2

            TRange.AutoFilter Field:=k, Criteria1:=Target.Value

End If
End Sub

I can Format T as Currency permanently??

Replace the whole code or just add the red code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim S, r As Long, c As Long, i As Long, j As Long, k As Long, n As Long
    Dim TRange As Range, Totals As Range, ws As Worksheet: Set ws = ActiveSheet
        r = 2: Do Until ws.Cells(r + 1, 2) = "": r = r + 1: Loop
        c = 15: Do Until ws.Cells(1, c + 1) = "": c = c + 1: Loop
        Set TRange = ws.Range(ws.Cells(1, 1), ws.Cells(r, c))
    Set Totals = ws.Range(ws.Cells(r + 2, 1), ws.Cells(r + 2, c))
                    Totals.Clear: S = Totals
                        ws.AutoFilterMode = False
            If Target.Column < 20 And Target.Value <> "" Then
                            k = Target.Column
        If k < 8 Then S(1, k) = Target.Value Else S(1, 7) = ws.Cells(1, k)
                            For i = 2 To r
            If ws.Cells(i, Target.Column) = Target.Value Then
            n = n + 1: S(1, 5) = S(1, 5) + ws.Cells(i, 5)
        For j = 10 To 17:
        If ws.Cells(i, j) <> "" Then
        S(1, j) = S(1, j) + 1: End If
        Next j
        For j = 20 To 22: S(1, j) = S(1, j) + Val(ws.Cells(i, j)): Next j
                           End If: Next i
                           S(1, 5) = S(1, 5) / n
                
    Totals = S: Totals.Interior.ColorIndex = 1: Totals.Font.ColorIndex = 2
                ws.Cells(r + 2, 5).NumberFormat = "0.0"
        ws.Cells(r + 2, 20).Resize(1, 3).NumberFormat = "$#,##0.00"
    
            TRange.AutoFilter Field:=k, Criteria1:=Target.Value

End If
End Sub

You're welcome again - perhaps you can write some tweaks with what I've shown you?