Finding MODE w/ text in cells

[brooksc29]

I am trying to find the MODE, or most common letter in this case, within a range (G4:U80) based on criteria in G5:U81.

right now this is my formula an array
{=MODE(IF(C2:C78="SMITH",IF(G2:U78=1,IF(G3:U79="A",IF(ISNUMBER(G4:U80),G4:U80)))))}

it is returning #N/A

is there a better way to find what I am looking for without MODE? I thought MODE will only find a numerical value and when using text it needs to be something else.


many thanks

[Domenic]

The following custom function returns the mode for data that contains text and/or numerical values. However, when the data contains only numerical data, use the native function MODE instead. It's probably more efficient.

1) Place the code for the custom function in a regular module...

Function MyMode(X As Variant) As Variant

    Dim Y As Variant
    Dim MyArray() As Variant

    If TypeOf X Is Range Then
    
        Set X = Intersect(ActiveSheet.UsedRange, X)
        
        For Each Y In X.Cells
            With WorksheetFunction
                If .IsNumber(Y) Or .IsText(Y) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = Y
                End If
            End With
        Next Y
    
    ElseIf IsArray(X) Then
    
        For Each Y In X
            With WorksheetFunction
                If .IsNumber(Y) Or .IsText(Y) Then
                    Cnt = Cnt + 1
                    ReDim Preserve MyArray(1 To Cnt)
                    MyArray(Cnt) = Y
                End If
            End With
        Next Y
        
    End If
    
    With Application
        MyMode = .Index(MyArray, .Mode(.Match(MyArray, MyArray, 0)))
    End With
    
End Function

2) Then try the following worksheet formula...

=MyMode(IF(C2:C78="SMITH",IF(G2:O78=1,IF(G3:O79="A",IF(ISNUMBER(G5:O81),G4:O80)))))

...confirmed with CONTROL+SHIFT+ENTER. Adjust the ranges, accordingly. Also, here are other possible worksheet formulas...

=MyMode(A2:G100)

=MyMode(A:D)