# Finding MODE w/ text in cells

This is a discussion on Finding MODE w/ text in cells within the Excel Questions forums, part of the Question Forums category; I am trying to find the MODE, or most common letter in this case, within a range (G4:U80) based on ...

1. ## Finding MODE w/ text in cells

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

2. ## Re: Finding MODE w/ text in cells

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

Code:
```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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•