Linked Questions

Popular Questions

Countif User Defined Function returns #Value

Asked by At

I am trying to create a vba countifs function that has mutliple criteria in different columns. I need it to only count the cells in Column E that have an interior color if the corresponding row in column C has a specific text value.

Essentially it is a leave calendar but I need it to count who is gone (indicated by color) but only if they have a specific qualification (indicated by text in column C) For instance: Only count cell E10 if C10 has value "TL" and E10 has interior color Green

I have tried many different codes but they all return #VALUE! Below are the examples I have tried:

 Function countifs_by_QASO(Cl As Range, Ct As Variant, Dy As Range, clr As 
 Variant) As Variant
 Application.Volatile True

 Dim x As Long
 Dim Cert As Range
 Dim Day As Range
 Dim Level As Variant
 Dim criteria_color As Variant

 Set Cert = Range("C21:C101")
 Set Day = Range("E21:E101")
 criteria_color = criteria.Interior.color

 x = 0

 For Each Cert In Cl
     If Level = Ct Then
     x = x + 1
End If

countifs_by_QASO = x

End Function

Next I tried:

 Function countifs_by_QASO(Cert As Range, Level As Variant, Day As Range, 
 color As Range) As Variant
     Application.Volatile True

 Dim y As Long
 Dim cel As Range
 Dim cel2 As Range

 criteria_color = criteria.Interior.color

 y = 0

 For Each cel In Day
     If cel.Interior.color = color.Interior.color Then
     y = y + 1
     End If
     Next

 For Each cel2 In Cert
     If cel = Level Then
     y = y + 0
     Else
     y = y - 1
     End If

 Next

 countifs_by_QASO = y

Finally I tried to incorporate an Offset property here:

Function countifs_by_QASO(Day As Range, color As Range) As String
Application.Volatile True

 Dim y As String
 Dim cel As Range
 Dim Level As Range
 color = criteria.Interior.color
 Level = Range("C21:C101")
 y = 0

 For Each cel In Day
     If cel.Interior.color = color.Interior.color Then
             If cell.Offset(0, Level).Value = "QASO" Then
                 y = y + 1
             End If
     End If

 Next

 countifs_by_QASO = y

 End Function

They all return #Value! which I am sure is a reflection of my VBA inexperience

When used, the formula will need to do the folowing:

step 1- look down its column

step 2- if a cell has the specified interior color then

step 3- offset in the same row to Column C and

step 4- if that cell has the specified text then

step 5= count +1

Related Questions