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...

[font=Verdana][color=darkblue]Function[/color] MyMode(X [color=darkblue]As[/color] [color=darkblue]Variant[/color]) [color=darkblue]As[/color] [color=darkblue]Variant[/color]

    [color=darkblue]Dim[/color] Y [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]Variant[/color]

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

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)