Find the most repeated value but only in cells of certain color in excel

Question

Hi,

I know this formula here

=MODE(!B:B)

gives you the most repeated values in column B but I want to count only colored cells. Is that even possible?

Thank you.

EDIT:

this is how my module looks:

Function GetInfo(TopObj As Variant, PropertySpec As Variant) As Variant

Dim PropArr As Variant ' array returned by Split of object tree
Dim ItemSpec As Variant ' item in collection
Dim Obj As Object ' generic Object to hold
                  'the top-level object (ws,wb,range, or app)
Dim Ndx As Long ' loop counter
Dim Pos1 As Integer ' used to find the Item specified in collection objects
Dim Pos2 As Integer ' used to find the Item specified in collection objects
Dim TempObj As Object

'
' split the object/property spec
'
PropArr = Split(PropertySpec, ".")
'
' If Rng is an object, then it must be a Range. That's the only
' type of object you pass from a cell.
'
If IsObject(TopObj) Then
    Set Obj = TopObj
Else
    '
    ' Otherwise, it better be one of the following strings. Else,
    ' blow up the user.
    '
    Select Case UCase(TopObj)
        Case "APP", "APPLICATION"
            Set Obj = Application
        Case "WB", "TWB", "THISWORKBOOK", "WORKBOOK"
            Set Obj = ThisWorkbook
        Case "WS", "TWS", "THISWORKSHEET", "WORKSHEET"
            Set Obj = Application.Caller.Parent
        Case Else
            GetInfo = CVErr(xlErrValue)
    End Select
End If

For Ndx = LBound(PropArr) To UBound(PropArr) - 1
    '
    ' this block of code is for handling items of a collection
    '
    Pos1 = InStr(1, PropArr(Ndx), "(")
    If Pos1 > 0 Then
        '
        ' if we've found the open paren, we're dealing with an
        ' item of a collection. now, find the closing paren.
        '
        Pos2 = InStr(1, PropArr(Ndx), ")")
        ItemSpec = Mid(PropArr(Ndx), Pos1 + 1, Pos2 - Pos1 - 1)
        If IsNumeric(ItemSpec) Then
            ' numeric -- going by index number
            ItemSpec = CLng(ItemSpec)
        Else
            ' string -- going by key name, so get rid of any quotes.
            ItemSpec = Replace(ItemSpec, """", "")
        End If
        '
        ' call the Item method of the collection object.
        '
        Set Obj = CallByName(Obj, Mid(PropArr(Ndx), 1, Pos1 - 1), _
            VbGet, ItemSpec)
    Else
        '
        ' we're not dealing with collections. just get the object.
        '
        Set Obj = CallByName(Obj, PropArr(Ndx), VbGet)
    End If
Next Ndx
'
' get the final property (typically 'name' or 'value' of the object tree)
'
If IsObject(Obj) Then
    GetInfo = CallByName(Obj, PropArr(UBound(PropArr)), VbGet)
End If

End Function

Public Function getArrayInfo(rng As Range, atr As String) As Variant
Dim temp As Excel.Range
Dim out() As Variant
Dim i As Long
i = 1

ReDim out(1 To rng.Rows.Count, 1 To 1)
Set temp = Intersect(rng, ActiveSheet.UsedRange)

For Each Item In temp.Cells
    out(i, 1) = GetInfo(Item, atr)
    i = i + 1
Next Item

getArrayInfo = out

End Function

Answer 1

after you have imported the getInfo function to your module, you now need to add an aditional function to the module to work with Array Formulas. Add this one after the getInfo function:

Public Function getArrayInfo(rng As Range, atr As String) As Variant
Dim temp As Excel.Range
Dim out() As Variant
Dim i As Long
i = 1

ReDim out(1 To rng.Rows.Count, 1 To 1)
Set temp = Intersect(rng, ActiveSheet.UsedRange)

For Each item In temp.Cells
    out(i, 1) = GetInfo(item, atr)
    i = i + 1
Next item

getArrayInfo = out

End Function

Then, in your worksheet you get the mode with:

=MODE(IF(getArrayInfo(data,"Interior.Color")=24,data))

where data is your data column. Remember to enter it as an array formula with Ctrl+Shift+Enter

Here I tested it with this data set:

ALTERNATE SOLUTION:

This solution assumes you are able to slightly modify your data, specifically adding a helper column and transforming the range to a table, but is much simpler, runs faster and doesn't require VBA.

1. Go to Formulas > Defined Names > Name Manager

2. Click on New, name it anything you want, I chose "bg" and in "Refers to:" type:

=GET.CELL(63,INDIRECT("rc[-1]",FALSE))

Then click ok and close the Name Manager.

3. Select your data table and go to Insert > Tables > Table, you'll see a dialog box to confirm the range you want to select, check if your table has headers and click ok, your data should now have table formatting. It's easy to recognize because now you should have a filter arrow next to your table header.

4. add a new header to the right of your data column then type color in the header. In the first data record on this new column, type the formula =bg (or whatever you chose to name your custom named range on step 1). Click enter and it should autofill with the same formula on the entire column:

5. Now finally, you have a helper column that reads the colorIndex for each corresponding record, so you can read which colorIndex you want to analize and your formula is simply:

{=MODE.SNGL(IF(Table1[COLOR]=ColorIndex,Table1[INPUT]))} where colorIndex is the number of the color you want to analyze, for example in my table yellow is 6 and red is 3. Remember to enter it as an array formula with Ctrl+Shift+Enter

Here you can see I have calculated the corresponding mode for all the colors in my data table, with the added benefit that since the data is formated as a table, our formulas will automatically update when we add a new record