How to count, sum and subtotal values in a filtered listbox?

I have a userform where I filter things in a listbox that is populated using an array.

I am trying to COUNT values in the array, SUM the values in the array or use SUBTOTAL function.

Code:

---

Myarray = Sheet61.ListObjects("Data").DataBodyRange.Value

---

The array has 21 columns. And I want to use SUBTOTAL(109, range)on a specific column?

How can I use subtotal formula on the array/listbox/listbox.list?

Code:

---

With ListBox1
            .List = Myarray
End With

---

I think that I should use

Code:

---

Label49.Caption = Application.WorksheetFunction.Subtotal(109, Listbox1.List(i,5))

---

Where I am not sure on how to get the range in the filterad listbox?

Code:

---

Dim j As Long
     
    For j = ListBox1.ListCount - 1 To 0 Step -1
    Me.TextBox5.Text = Application.WorksheetFunction.Subtotal(109, ListBox1.List(j, 8))
    Next

---

I am not sure why this doesn't work?

Not tested

Code:

---

    Dim j As Long, N as long
     
    For j = ListBox1.ListCount - 1 To 0 Step -1
        n = n + ListBox1.List(j,8)
    Next j

    Me.TextBox5.Text = N

---

Or work with the ListObject maybe

Code:

---


N = Application.WorksheetFunction.Sum(Sheet61.ListObjects("Data").DataBodyRange.Columns(8))

---

(also not tested)

Hi again Paul,

the first one works great! Thank you.


To get the count use:

Code:

---


Me.TextBox6 = Listbox1.ListCount

---