# 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

#### Attachments

• SS_1.png
3.7 KB · Views: 5
• SS_02.png
20.3 KB · Views: 4
• SS_03.png
42.5 KB · Views: 5

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Replies
29
Views
504
Replies
8
Views
194
Replies
35
Views
328
Replies
3
Views
637
Replies
0
Views
104

1,148,274
Messages
5,745,797
Members
423,974
Latest member
highvoltageacdc

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back