Conditional Formulas that ommit a certain color fill

[PADWIS]

I have a range with RED, GREEN and AMBER. I want to get a sum of the entire range but be able to NOT INCLUDE a certain color such as RED. I amd stuck on what to do. Can anyone get me going on this?

 

[Jonmo1]

Welcome to the board.

Are the cells colored by conditional formatting?
If so, you can use the same (or opposite) logic from the conditional formatting formula.

Can you post the CF formulas that you have?

 

[piguy]

use the ozgrid colorfunction and call into conditional format as a formula

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult
 
     ''''''''''''''''''''''''''''''''''''''
     'Written by Ozgrid Business Applications
     'www.ozgrid.com
 
     'Sums or counts cells based on a specified fill color.
     '''''''''''''''''''''''''''''''''''''''
 
    lCol = rColor.Interior.ColorIndex
 
    If SUM = True Then
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = WorksheetFunction.SUM(rCell, vResult)
            End If
        Next rCell
    Else
        For Each rCell In rRange
            If rCell.Interior.ColorIndex = lCol Then
                vResult = 1 + vResult
            End If
        Next rCell
    End If
 
    ColorFunction = vResult
    Application.Volatile
End Function

then CF in cell looks like so

=AND(ColorFunction($A$1,I2,FALSE)

A1 is the reference cell for that formula