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

[waimea]

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.

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?

With ListBox1
            .List = Myarray
End With

I think that I should use

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?

[waimea]

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?

[Paul_Hossler]

Not tested

    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

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

(also not tested)

[waimea]

Hi again Paul,

the first one works great! Thank you.


To get the count use:

Me.TextBox6 = Listbox1.ListCount