Count by color only if another condition holds

[g579]

Hi,



I've been using a user defined function that I found in the forum below and I would like to manipulate it to make more use out of it and I was wondering if you know how to do this. What I am trying to do is basically count the number of cells that are green in one column only if the corresponding cell in another column contains a certain text like "Office".

Basically, I only want to count the green cells in say column A if the corresponding cell in column B contains "Office". I also would like it to refresh automatically every time a change happens.

Thank you in advance for your help!!

Public Function CountIfColor(rng As Range, clrindx As Integer)
Dim Cell
CountIfColor = 0
For Each Cell In rng.Cells
If Cell.Interior.ColorIndex = clrindx Then
CountIfColor = CountIfColor + 1
End If
Next Cell
End Function

To return the quantity of red-shaded (non-conditionally formatted) cells in the range A1:A100 for example, this is the formula you'd type into a cell:

=CountIfColor(A1:A100,3)

 

[Norie]

Well that's one test I didn't try, but I'm not surprised it's not updating.

Formatting a cell isn't going to trigger calculation, which is one of the reasons that it's not a good idea to count by colour/formatting.