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 :

1. N° of projects "closed" ..... 'the result was wrong number as I think it takes from one sheet only
2. N° of projects "OPEN"......
3. 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"

Untitled.jpg

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:

Capture.jpg Capture2.jpg


Hope my idea is clear now.

Waiting for your valuable help

[djemy1975]

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

[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?