# VBA CountIf function results in "0"

#### PlutoQX

##### New Member
Hello all,

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 of Wb(Screenshot 2) and an example of rows of my wb.Source.(Screenshot 3)

2.
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?

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``````

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Replies
0
Views
107
Replies
3
Views
178
Replies
8
Views
99
Replies
41
Views
3K
Replies
6
Views
113