Count Listview subitems based on condition

[djemy1975]

Dear developpers,

I have a userform based on listview display of three sheets ,and I want to make some statistics on the form showing the numbers of ocuurence of certain text values in a specified column (in sheets("AU")and (47) in listview.

as follows :


N° of projects "closed" ..... 'the result was wrong number as I think it takes from one sheet only
N° of projects "OPEN"......
N° of projects "to close".....


I have tried with this syntaxe in my userform initialize ,but to no avail:

Private Sub UserForm_Initialize()
Call Create_Lists

ChartNum = 1

Dim WS As Worksheet
Dim lngRow As Long
Dim lvwItem As listItem
Dim lngCol As Long
Dim rSheet As Range

With ComboBox6
.List = Array("Basic to Sustain", "Specific to sustain", "Improve-performance")
End With

With ComboBox1
.List = Application.WorksheetFunction.Transpose(Worksheets("Basic to Sustain").Cells(1, 1).CurrentRegion.Rows(1))
End With


With ListView1
.View = lvwReport
.Gridlines = True
.HideSelection = False
.FullRowSelect = True
.HotTracking = True
.HoverSelection = False
.ColumnHeaders.Clear

Set WS = Worksheets("Basic to Sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion

rSheet.EntireColumn.AutoFit

For lngCol = 1 To rSheet.Columns.Count
.ColumnHeaders.Add , , WS.Cells(1, lngCol).Value, WS.Columns(lngCol).ColumnWidth * 10
Next lngCol




For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)


For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow


Set WS = Worksheets("Specific to sustain")
Set rSheet = WS.Cells(1, 1).CurrentRegion


For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)


For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow


Set WS = Worksheets("Improve-performance")
Set rSheet = WS.Cells(1, 1).CurrentRegion


For lngRow = 2 To rSheet.Rows.Count
Set lvwItem = .ListItems.Add(, , WS.Cells(lngRow, 1).Value)


For lngCol = 2 To rSheet.Columns.Count
lvwItem.ListSubItems.Add , , WS.Cells(lngRow, lngCol).Value
Next lngCol
Next lngRow


End With
Call CondFormat
Me.TextBox2.Value = ListView1.ListItems.Count
LV_AutoSizeColumn ListView1
'count the payment status
'Here is my problem:
Label28 = Application.WorksheetFunction.CountIf(rSheet, "clôturé")
Label29 = Application.WorksheetFunction.CountIf(rSheet, "en cours")
Label7 = Application.WorksheetFunction.CountIf(rSheet, "a clôturer")
Label30 = Application.WorksheetFunction.CountIf(rSheet, "En attende Validation Group")

End Sub

Your help is highly appreciated

[djemy1975]

Dear friends,

I have found a solution as follows:

Label28 = Application.WorksheetFunction.CountIf(Sheets("Basic to Sustain").Range("AU:AU"), "clôturé") + Application.WorksheetFunction.CountIf(Sheets("Specific to sustain").Range("AU:AU"), "clôturé") + Application.WorksheetFunction.CountIf(Sheets("Improve-performance").Range("AU:AU"), "clôturé")
Label29 = Application.WorksheetFunction.CountIf(Sheets("Basic to Sustain").Range("AU:AU"), "en cours") + Application.WorksheetFunction.CountIf(Sheets("Specific to sustain").Range("AU:AU"), "en cours") + Application.WorksheetFunction.CountIf(Sheets("Improve-performance").Range("AU:AU"), "en cours")
Label7 = Application.WorksheetFunction.CountIf(Sheets("Basic to Sustain").Range("AU:AU"), "a clôturer") + Application.WorksheetFunction.CountIf(Sheets("Specific to sustain").Range("AU:AU"), "a clôturer") + Application.WorksheetFunction.CountIf(Sheets("Improve-performance").Range("AU:AU"), "a clôturer")
Label30 = Application.WorksheetFunction.CountIf(Sheets("Basic to Sustain").Range("AU:AU"), "En attende Validation Group") + Application.WorksheetFunction.CountIf(Sheets("Specific to sustain").Range("AU:AU"), "En attende Validation Group") + Application.WorksheetFunction.CountIf(Sheets("Improve-performance").Range("AU:AU"), "En attende Validation Group")

But this solution is working with sheets only because when I apply filter ,counted values stay unchanged.

The question is what is the alternative of this code when we want to use listview depending on the above userform intialize code.

Waiting for your valuable help,

[Kenneth Hobs]

There may be better ways but this should work. It is not case sensitive.


'https://www.mrexcel.com/forum/excel-questions/45637-have-formula-consider-only-visible-rows.html

'=COUNTifvis(B:B,"*ray*")
Function CountIfVis(r As Range, v As Variant) As Long
Dim a As Range, s As Long
s = 0
For Each a In Vis(r).Areas
s = s + WorksheetFunction.CountIf(a, v)
Next a
CountIfVis = s
End Function


Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

[djemy1975]

Thank you Sir.I am trying to test it but I could not found how .I want to count the number of occurence of specific text values in Listview ListSubItems(46) .These text values should be shown on userform using labels
Label28 = "clôturé"
Label29 = "en cours"
Label7 = "a clôturer"
Label30 = "En attende Validation Group"

23311

This calculation should be done with Listview rather than sheets so that when I FILTER values in statistics change accordingly.
Best regards,

[Kenneth Hobs]

I can't really tell anything from a screen snip. I don't know what filter means, filter in the listview control or filter in the sheets.

Obviously, if you filter by sheets, the method that I showed would work.

[djemy1975]

Dear Sir,

Herewith my project sample with less lines because it contains nearly 200 lines :

- What I want to do is statistics based on userform initialize and and when applying filter with search button in my userform as follows:
So that the code should be called from two places :Userform initialize and search Button.

These statistics are based on 4 criteria IN COLUMN (46) named "Status" in my Listview1:
"clôturé"
"en cours"
"a clôturer"
"En attende Validation Group".

Screenshots to better understand:

23316 23317


Hope my idea is clear now.

Waiting for your valuable help

[djemy1975]

Is there any possibilité or shall I abondon this idea?:crying:

[Kenneth Hobs]

It is a nice form but pretty involved. I will have to study it to see if I can understand what you want.

I am not sure where you want some count to be shown. I tried what seemed a valid filter criterion but Search button did not show the result that I expected. IF Search button worked, ListView's listcount property should give the count needed?

[djemy1975]

Is there any ideas concerning this thread?