PDA

View Full Version : [SOLVED] How to count, sum and subtotal values in a filtered listbox?



waimea
10-08-2019, 07:52 AM
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
10-08-2019, 08:30 AM
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
10-08-2019, 09:03 AM
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
10-08-2019, 09:16 AM
Hi again Paul,

the first one works great! Thank you.


To get the count use:




Me.TextBox6 = Listbox1.ListCount