Error with Countifs function

[raeannb]

Hi! I'm having a CountIf problem (grrrr that darn function!) that I'm hoping someone could help me with. The following code is the 3rd or 4th version I've tried to get working, and they all return errors. Right now I'm getting a "Unable to get the CountIfs property of the WorksheetFunction class" error at the starred line:

If Not WB2.Sheets(i).Rows(6).Find(What:="Start", LookAt:=xlPart, SearchOrder:=xlByColumns) Is Nothing Then
StartColumn = WB2.Sheets(i).Rows(6).Find(What:="Start", LookAt:=xlPart, SearchOrder:=xlByColumns).Column
NextColumn = WB2.Sheets(i).Rows(7).Find(What:="Next", LookAt:=xlPart, SearchOrder:=xlByColumns).Column
Set myRange = WB2.Sheets(i).Range(WB2.Sheets(i).Cells(j, StartColumn), WB2.Sheets(i).Cells(j, NextColumn - 1))
If IsEmpty(WB1.Sheets(2).Range("L" & SiteRow)) = True Then
a = WorksheetFunction.CountIfs(myRange, "<>" & "NC", "<>" & "NA", "<>" & "") *****Error here
b = WorksheetFunction.CountIf(myRange, "<>" & "NA")
WB1.Sheets(2).Range("L" & SiteRow).Value = a & "/" & b
End If
End If

The range is contigious, so that shouldn't be the issue...any thoughts? Thank you!!

 

[RoryA]

It's the same syntax as in a cell - you need to repeat the range for each criterion:

a = WorksheetFunction.CountIfs(myRange, "<>NC", myRange, "<>NA", myRange, "<>" & "")

 

[raeannb]

It worked! I'm an idiot, and you are awesome. Thank you tons and have a happy holiday!