Help: Countif Specific Value & Formatting

[synergeticink]

Hello, I have been searching the web all morning trying to find a solution with no luck. I'm assuming VBA will be needed to pull this off.

Basically I am trying write a formula that checks one cell on my sheet for a specific value, and counts the amount of times the value appears in column G. The problem is I have the same values in that column but have different fill colors, that I would like to exclude from the count.

[JohnTopley]

If you need to check colours then you will need VBA.

It would help if you could post a small sample fill explaining your requirement,

[synergeticink]

Well...

at first I started with a simple COUNTIF formula, which works if I am trying to find the exact value.

=COUNTIF(A:A,A2)


Then I found a way to search by color using VBA.

FORMULA:
=countccolor(G6:G1000,B8)

VBA:
Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function


The problem is that neither of these can count based on the value and color fill of the referenced cell.

[JohnTopley]

The Attached code will count a cell with given color and containing text

Please Login or Register  to view this content.

=countCcolorX(A1:A8,C1,"G")

counts cells containing "G" in cells with color of C1

[synergeticink]

Thank you, this works!

I have one question, the count does auto update if I add/remove another instance of the value to the range, but it doesn't update the count if I change the color of one of the cells in the range manually. I have to click in the cell with the formula and press enter to get it to update the count. Any ideas on how to make the formula auto update?

[JohnTopley]

I am not aware of any mechanism that will do this - it will only recalculate when you press enter.

[synergeticink]

Thank you so much!

[JohnTopley]

Could mark thread as solved ("Thread Tools" at top of first post). Thank you.