Count most frequently occurring text
To count the most frequently occurring text you can use a combination of the Excel COUNTIF, INDEX, MODE, MATCH and IF functions that can be applied into Excel or VBA
Example: Count most frequently occurring text
|
The formula uses a combination of the Excel COUNTIF, INDEX, MODE and MATCH functions to count the most frequently occurring text in a specified range.
Given the Excel MATCH function returns the position of the first match, by applying the same range to the lookup value and the lookup array the MATCH function will return a range of results (numbers) where each number represents the first occurring position of the value. The formula then applies the Excel MODE function to return the most frequently occurring number from the range of numbers calculated through the use of the Excel MATCH function. In this example the most occurring number will be (1) associated with Bread. The Excel INDEX function is used to identify the most frequently occurring number. Using the same range as was applied in the Excel MODE and MATCH functions (B5:B9) it would return the first value in the range, which in this example is Bread. The final step is to count the most frequently occurring text by using the Excel COUNTIF function and the most frequently occurring text against the range from which you want to count the most frequently occurring text. Please note that this formula will return an #NA error if at least one of the cells in the selected range is blank. To account for this you need to apply an array formula, shown in the Excel Method 2. |
METHOD 2. Count most frequently occurring text if range contains blank cells
EXCEL
|
This is an array formula that can be used if the range that you are selecting includes blank cells. This formula uses the Excel COUNTIF, INDEX, IF, MODE and MATCH functions to count the most frequently occurring text. The principles of this formula is basically the same as the formula in the Excel Method 1. The difference between the two formulas is that this formula uses the IF statement to test for blank cells.
Please note that this is an array formula, therefore once you have entered the formula you must press Ctrl + Shift + Enter keys simultaneously. |
Dim rng As Range
Dim selectrng As Range
Dim ws As Worksheet
Set selectrng = Range("B5:B9")
With CreateObject("scripting.dictionary")
.Item(rng.Value) = .Item(rng.Value) + 1
countfreq = .Item(rng.Value)
countfreqtext = countfreq
End If
End If
Next
End With
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named Analysis.
Range: In this example we are assessing range ("B5:B9") for the most frequently occurring text.
Output Range: Select the output range by changing the cell reference ("E4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Range: Select the range from where you want to count for the most frequently occurring text by changing the range ("B5:B9") in the VBA code to any range in the worksheet, that doesn't conflict with the formula.
ADDITIONAL NOTES
Note 1: This VBA code accounts for blank cells in the specified range. Therefore, the VBA code will still count the most frequently occurring text even if there are blank cells in the selected range.
This tutorial shows and explains how to count the most frequently occurring text in a range, by using Excel formulas and VBA.
Excel Methods: This tutorial provides two Excel methods that can be applied to count the most frequently occurring text in a range.
The first method uses a combination of the Excel COUNTIF, INDEX, MODE and MATCH functions to count the most frequently occurring text in a range. The formula applied for this method will return an #NA error if at least one of the cells in the range is blank.
The second method uses a combination of the Excel COUNTIF, INDEX, IF, MODE and MATCH functions to count the most frequently occurring text in a range. This method is principally the same as the first method, however, the formula applied in this method will account for blank cells. Therefore, if the selected range have a blank cell the formula will still count the most frequently occurring text. The formula in this method is an array formula, therefore when you have entered the formula you must press Ctrl + Shift + Enter keys simultaneously.
VBA Method: This tutorial provides one VBA method that can be applied to most frequently occurring text in a range.
=COUNTIF(range,INDEX(range,MODE(MATCH(range,range,0))))
{=COUNTIF(range,INDEX(range,MODE(IF(range<>"",(MATCH(range,range,0))))))}
ARGUMENTS
range: The range from which to count the most frequently occurring text.
| Related Topics | Description | Related Topics and Description |
|---|---|---|
| Return most frequently occurring text | To return the most frequently occurring text you can use a combination of the Excel INDEX, MODE, MATCH and IF functions that can be applied into Excel or VBA |
| Related Function | Description | Related Functon and Description |
|---|---|---|
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria | |
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| MATCH Function | The Excel MATCH function searches for a specified value in a range and returns its position relative to the range | |
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE |