Show Total row in listbox based on search separated range

Malkal

You've asked for a strange "total" in your ListBox where:

1. If ComboBox3 produces a Listbox with one row, the "total" is that row
2. If ComboBox3 produces a Listbox with more than one row, the "total" is the sum of rows EXCLUDING the first row.

In the attached file, I've modified the sub FilterData (as shown in bold below) so that:

1. A new variable FTotal collects that "total" where data matches
2. If all Comboxes are completed (and there's filtered data), a Total is displayed as the last row of the ListBox (with a note, if row 1 is omitted)
3. (As an extra) the date column is formatted in dd mmm yyyy for less-ambiguous reading.

Sub FilterData()
  Dim txt1 As String, txt2 As String, txt3 As String
  Dim i As Long, j As Long, k As Long
  Dim FTotal As Double

  FTotal = 0
  ListBox1.Clear
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

  For i = 1 To UBound(a)
    If ComboBox1 = "" Then txt1 = a(i, 2) Else txt1 = ComboBox1
    If ComboBox2 = "" Then txt2 = a(i, 3) Else txt2 = ComboBox2
    If ComboBox3 = "" Then txt3 = a(i, 4) Else txt3 = ComboBox3
    If LCase(a(i, 2)) Like LCase(txt1) & "*" And _
       LCase(a(i, 3)) Like LCase(txt2) & "*" And _
       LCase(a(i, 4)) Like LCase(txt3) & "*" Then

      k = k + 1
      'For j = 1 To 5
      ' change loop to make first entry a date format
      b(k, 1) = Format(a(i, 1), "dd mmm yyyy")
      ' then collect other values
      For j = 2 To 5
        b(k, j) = a(i, j)
      Next j

        ' update FTotal
        If k <= 2 Then
            If IsNumeric(a(i, 5)) Then FTotal = a(i, 5)
            Else
            If IsNumeric(a(i, 5)) Then FTotal = FTotal + a(i, 5)
        End If

    End If


  Next i
    If k > 0 Then ListBox1.List = b

    ' only if all boxes are complete...
    If ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 <> "" And k > 0 Then
      ' ...add the total row
      ListBox1.addItem
      If k = 1 Then
        ListBox1.List(k, 0) = "Total"
        Else
        ListBox1.List(k, 0) = "Total (less first row)"
        End If
      ListBox1.List(k, 4) = FTotal
    End If

End Sub

I notice that your entries in worksheet DETAILS do not match all combinations in DATA (but leave you to correct that / check other combinations work).

Hope this works well for you.