# countif to find average

#### anisurrahman

##### New Member
Hi

This is my first post.

I have five columns Column (H,I,J,K,L), I am trying to check these columns cell value and then find average. I can use excel formulae [=M11/COUNTIF((H11:L11),">0")], but I need to convert this formulae into VBA.

Code:
``````  ' ------ code doesn't work ----------
countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
Cells(myRow, "L"), ">0")

'APPLICATION-DEFINED OR OBJECT-DEFINED ERROR``````

My full code ----
Code:
``````Private Sub saveHolButton_Click()
Dim myRow
'to find row
Dim sfind  As String
Dim cl     As Range
Dim remainingHours As Double, totalhoursWork As Double

With Sheets("MasterData")

myRow = Application.Match(Sheets("Temp").[A11], .[A:A], 0)
'find the userID Row

If IsNumeric(myRow) Then
'if found userID

.Cells(myRow, "Q") = Sheets("Temp").[c17]
'assign the contents of C17 of sheet Temp to corresponding cell in MasterData sheet

.Cells(myRow, "S") = .Cells(myRow, "Q") + .Cells(myRow, "R")
' sum of total booked holiday January to December

.Cells(myRow, "T") = .Cells(myRow, "P") - .Cells(myRow, "S")
remainingHours = .Cells(myRow, "T")

totalhoursWork = .Cells(myRow, "M")
' sum of column H,I,J,K and L
' How do I find average of Sum(H:L)
' To find average 5 columns cell value - so it should be sum(H:L)/5
'If one of the column cell value is 0 then it should sum(H:L)/4
'if two of the columns cell value is 0 then it should sum(H:L)/3
' I can use the formulae in Excel but how do i convert it into VBA
' Excel formula =M11/COUNTIF((H11:L11),">0")
' M11 is total of (H,I,J,K and L)

' ------ code doesn't work ----------
countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
Cells(myRow, "L"), ">0")

'APPLICATION-DEFINED OR OBJECT-DEFINED ERROR

worksheets("MasterData").Cells(myRow, "W") = .Cells(myRow, "T") / countWeekPattern

End If

End With
end sub``````

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### njimack

##### Well-known Member
Does the following approach not work for you?
Avg = WorksheetFunction.Average(Range("H11:L11"))

#### anisurrahman

##### New Member
hi thanks for your quick reply. problem is I want to check the cells value first, if one of the cell value is 0 then it should divide the total of 5 columns by 4, as I explained in my post. Because I am counting holiday entitlements, so it is important to know whether the cell value is 0 or not. And my row for example H11 is not fixed it is actually cells(myrow,8), so I wanted to use countif

countWeekPattern = Application.CountIf(Sheets("MasterData").Range(Cells(myRow, "H"), _
Cells(myRow, "I")), Cells(myRow, "J"), Cells(myRow, "K"), _
Cells(myRow, "L"), ">0")

Any idea ..

#### njimack

##### Well-known Member
It might be failing because you're not qualifying the sheet with each use of a range. Maybe something like this?

With Sheets("MasterData")
countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), _
.Cells(myRow, "I")), .Cells(myRow, "J"), .Cells(myRow, "K"), _
.Cells(myRow, "L"), ">0")
End With

#### anisurrahman

##### New Member
It might be failing because you're not qualifying the sheet with each use of a range. Maybe something like this?

With Sheets("MasterData")
countWeekPattern = Application.CountIf(Range(.Cells(myRow, "H"), _
.Cells(myRow, "I")), .Cells(myRow, "J"), .Cells(myRow, "K"), _
.Cells(myRow, "L"), ">0")
End With

I tried and still it is showing Runtime Error '1004', Application-Defined or object-defined error.

Sorry any Idea

#### anisurrahman

##### New Member
any help from anyone Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,275
Messages
5,836,337
Members
430,421
Latest member
Natas ### 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