Conditional count based on cell value and color

  • I am trying to do a search for both a part number and a cell color, while when finding both of these conditions i need a count of the number of times these both conditions are met?
    Any ideas would be appreciated greatly!

  • Re: Conditional count based on cell value and color


    Hello proengineer,


    Welcome to Ozgrid.


    If the cell is having it's color set using conditional formatting, then you should be able to get a count using this as one of the criteria.


    Possibly a SUMPRODUCT formula will work for this or, if using Excel 2007+, the COUNTIFS function.


    To get the most precise answers, it is best to upload a sample workbook (sensitive data scrubbed/removed) that shows a few manually created examples of the desired results.
    The structure and data types of the workbook must exactly match that of the real workbook. Include in the workbook a clear and explicit explanation of all requirements.The sample workbook only needs to contain enough data to illustrate the need to aid with developing the solution.


    [COLOR="navy"]How to edit your post or thread title or upload an attachment[/COLOR]


    1. Click the EDIT POST in the gray band immediately below your post
    2. Click Go Advanced
    3. Edit the post or thread title
    4. To upload: scroll down to Manage Attachments

  • Re: Conditional count based on cell value and color


    Equipment Model NumberPart #Part #Model # only in yellowSL280UH110V60100870-01100870-01& =How many timesG61MPV-60C-090-12100870-03100870-02& =How many timesG60UHV-36A-070-07100870-03100870-03& =How many timesG60DFV-36A-070-07100870-02SL280UH110V60100870-03G60UHV-36B-090-07100870-03G61MPV-60C-091-10100870-03All I want to do is look up and count the conditions where both there is a part # and a yellow cell for that part #.G61MPV-60C-110-10100870-03G61MPV-60C-110-07100870-03G60UHV-60C-110X-07100870-01G60UHV-36A-070-10100870-02SL280UH110V60100870-03G61MPV-60C-110-07100870-03

  • Re: Conditional count based on cell value and color


    How does the cell get its color, Conditional Formatting or does the user set the cell color?


    If the user sets the cell color, a count such as you seek can be done fairly easily. BUT, using a spreadsheet that uses color as data is (IMO) a poorly designed sheet.
    Sure Red is more important than Blue, but is Blue > Green or is it the other way round?
    Excel is not designed to use cell color as data. Excel uses cell color as a display aid for human eyes. Not as an input for data. Changing a cell's color does not trigger calculation. So a cell holding a UDF that counts cells of a certain color will be frequently inaccurate.


    As AAE said, counting the cells that meet the various conditions that set Conditional Formatting is easier than testing the CF directly.

  • Re: Conditional count based on cell value and color


    The user colors the cell as needed.
    I use a function to count the cells that are set to whatever color i need.
    Here is the color counting function:

    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

    lCol = rColor.Interior.ColorIndex
    If Count = True Then
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.Count(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
    End Function

    I use this to count the cells with the correct part # =COUNTIFS('4-21-11'!H:H,"=102688-01")
    But I cant figure out how to make the cell color and the part # both the conditions to count.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!