Countifs based on text and Color

[andrewc85]

Hello,

I am needing a formula to perform a countifs if Column A contains "Andrew" and Column B has the interior.colorindex =35

I have been using the below function which I think is quite commonly found on the web, and works fine but only counts column B and has no reference to column A or any other criteria.

Public Function GetColorIndex(Cell As Range)
  GetColorIndex = Cell.Interior.ColorIndex
End Function

Public Function CountColorIndexInRange(Rng As Range, TestColor As Long)
  Dim cnt
  Dim cl As Range
  cnt = 0

  For Each cl In Rng
    If GetColorIndex(cl) = TestColor Then
      Rem Debug.Print ">" & TestColor & "<"
      cnt = cnt + 1
    End If
  Next

  CountColorIndexInRange = cnt

End Function


Sheet11.Range("D4").Value = CountColorIndexInRange(Sheet11.Range("B7:B750"), 35)

I just need to help modifying the above formula to also incrportate a second condition for column A. There is also a sample screen shot below.

Many help would be greatly appreciated.

Cheers, Andrew

 

[yky]

Try replacing the only IF statement with the following:

If GetColorIndex(cl) = TestColor  And InStr(cl.Offset(0,-1),"Andrew",1) > 0 Then

 

[andrewc85]

Many thanks for the suggestion, but am now receiving Run-Time error 5. Invalid procedure call or argument.

I have the below sub to run the function which is when the error occurs. 35 references the colorindex.

Sub Test()
Sheet11.Range("D4").Value = CountColorIndexInRange(Sheet11.Range("N7:N750"), 35)

End Sub

 

[yky]

In the original post, you passed column B and wanted to check whether column A contained the word "Andrew". Now, you are passing column N. So, the offset is not correct. The offset is now referring to column M, not A. Even so, the error message, "invalid procedure call or argument", doesn't make sense. It seems to be saying the syntax of InStr call is incorrect. What'd you get if you do a debug print on InStr(cl.Offset(0,-1),"Andrew") or better yet, InStr(cl.Offset(0,-13),"Andrew")?

 

[andrewc85]

Sorry for the confusion, lets talk columns A & B as per previous message.

Unfortunately, still getting the same "invalid procedure call or argument", when running the below code.

Sub Test()
Sheet11.Range("D4").Value = CountColorIndexInRange(Sheet11.Range("A7:A750"), 35)
End Sub

Public Function GetColorIndex(Cell As Range)
  GetColorIndex = Cell.Interior.ColorIndex
End Function

Public Function CountColorIndexInRange(Rng As Range, TestColor As Long)
  Dim cnt
  Dim cl As Range
  cnt = 0

  For Each cl In Rng
    If GetColorIndex(cl) = TestColor And InStr(cl.Offset(0, -1), "Andrew", 1) Then
      Rem Debug.Print ">" & TestColor & "<"
      cnt = cnt + 1
    End If
  Next

  CountColorIndexInRange = cnt


End Function

 

[andrewc85]

Even changing the referenced cells to the below, I still get the error.

Sub Test()
Sheet11.Range("D4").Value = CountColorIndexInRange(Sheet11.Range("B7:B750"), 35)
End Sub

 

[andrewc85]

I solve the issue, the code should have been

If GetColorIndex(cl) = 35 And cl.Offset(0, -1).Value = "Andrew" Then

Many thanks for all your help getting me on the right track, its greatly appreciated!

 

[yky]

Glad you worked it out.