Hello all,

nice to join this community

I have a question regarding the countif function in VBA form.

I have a master workbook(Wb) with a user form - file explorer (Screenshot 1) and a Sheet2 for the results(Screenshot 2)

I have a source workbook, which has a huge amount of data(14k rows) and is quite frankly a mess.. For my calculation only 2 columns of the source workbook are relevant: column 8 (True Dates) and colum 30 (numerical values).

I use the Wb to search for the source workbook(wb.Source) to apply the following calculations:

This should be applied for every month.

The results are then added to my Wb Sheet2.

I would appreciate any help!

nice to join this community

I have a question regarding the countif function in VBA form.

**The actual situation**I have a master workbook(Wb) with a user form - file explorer (Screenshot 1) and a Sheet2 for the results(Screenshot 2)

I have a source workbook, which has a huge amount of data(14k rows) and is quite frankly a mess.. For my calculation only 2 columns of the source workbook are relevant: column 8 (True Dates) and colum 30 (numerical values).

**Important note regarding column 8 :**only the rows with the first of every month is relevant for me. Meaning: 01.01., 01.02.,01.03.,01.04. etc.**Every other row should be ignored.**I use the Wb to search for the source workbook(wb.Source) to apply the following calculations:

**Task 1**: calculating the avarage value if certain conditions are met**Task 2:**counting rows if the following conditions are met:**If**a row in wb.Source has the values in Column 8 = "01.01" and Column 30 is < 50**then**count and enter the result in cell B8 in Sheet 2 of Wb after the whole wb,Source is checked.**If**a row in wbSource has the values Column 8= "01.01" and Column 30 is > 50 and < 100**then**count and enter the result in cell B9 in Sheet 2 of Wb after the whole wb,Source is checked.**If**a row in wbSource has the values Column 8= "01.01" and Column 30 is > 100**then**count and enter the result in cell B10 in Sheet 2 of Wb after the whole wb,Source is checked.

This should be applied for every month.

The results are then added to my Wb Sheet2.

**My problems/questions**

1.Regarding Task 2: My CountIf function results in "0" for every month and I dont knw why? See attached screenshots from my result sheet of1.

**Wb**(Screenshot 2) and an example of rows of my**wb.Source.**(Screenshot 3)**Regarding Task 1: the calculation works so far, but how can I make sure that only the rows with the first of every month are considered and everything else ignored?**

2.2.

**3.**Is it possible to restrict the scope of the calculation in the sense that only the past 12 months are calculated, starting from a certain date(counting back).**Meaning**: To set a Date(Month/Year) in my user form and consider only the rows of the past 12 months for the calculation? If no date is set then "today" should be the starting point. I think the**Const = YEAR**has to be removed from the code, as there is definitely the possibility that the wb.Source contains rows from different years.I would appreciate any help!

**The code**
VBA Code:

```
Private Sub CommandButton1_Click() ' select file
Dim fname As Variant
With Me
fname = Application.GetOpenFilename("Excel File(s) (*.xls*),*.xls*", , "Select FIle", , False)
If fname <> "False" Then .TextBox1.Text = fname
End With
End Sub
'calculate avarage
Private Sub CommandButton2_Click() ' update averages
Const YEAR = 2019
' open source workbook
Dim fname As String, wbSource As Workbook, wsSource As Worksheet
fname = Me.TextBox1.Text
If Len(fname) = 0 Then
MsgBox "No file selected", vbCritical, "Error"
Exit Sub
End If
Set wbSource = Workbooks.Open(fname, False, True) ' no link update, read only
Set wsSource = wbSource.Sheets("Sheet1") ' change to suit
Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("Sheet2") '
' scan down source workbook calc average
Dim iRow As Long, lastRow As Long
Dim sMth As String, iMth As Long
Dim count(12) As Long, sum(12) As Long
lastRow = wsSource.Cells(Rows.count, 1).End(xlUp).Row
For iRow = 1 To lastRow
If IsDate(wsSource.Cells(iRow, 8)) _
And IsNumeric(wsSource.Cells(iRow, 30)) Then
iMth = Month(wsSource.Cells(iRow, 8)) ' col H
sum(iMth) = sum(iMth) + wsSource.Cells(iRow, 30) ' Col AD
count(iMth) = count(iMth) + 1 '
End If
Next
'calculate number of rows (CountIf)
Dim x As Long
Dim m As Variant
For x = 1 To 12
m = "01." & Format(x, "00")
ws.Cells(8, 1 + x) = _
Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
wsSource.Columns(30), "<=" & 50)
ws.Cells(9, 1 + x) = _
Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
wsSource.Columns(30), ">" & 50, wsSource.Columns(30), "<=" & 100)
ws.Cells(10, 1 + x) = _
Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
wsSource.Columns(30), ">" & 100)
Next x
' close source worbook no save
wbSource.Close False
' update Sheet 2 with averages
With ws.Range("A3")
For iMth = 1 To 12
.Offset(0, iMth - 1) = MonthName(iMth) & " " & YEAR
If count(iMth) > 0 Then
.Offset(1, iMth - 1) = sum(iMth) / count(iMth)
.Offset(1, iMth - 1).NumberFormat = "0.0"
End If
Next
End With
Dim msg As String
msg = iRow - 1 & " rows scanned in " & TextBox1.Text
MsgBox msg, vbInformation, "Sheet 2 updated"
End Sub
```