Return least frequently occurring text
This tutorial shows how to extract the least frequently occurring text using an Excel formula, with the INDEX, MATCH, MIN and COUNTIF functions
|
GENERIC FORMULA
=INDEX(range,MATCH(MIN(COUNTIF(range,range)),COUNTIF(range,range),0))
ARGUMENTS EXPLANATION This formula uses a combination of the INDEX, MATCH, MIN and COUNTIF functions to return the least frequently occurring text from a specified range. Given that this is an array formula, once the formula is entered you are required to press Ctrl + Shift + Enter at the same time, which will enclose the formula in { } signs.
In this example the text "purple" only appears once in the selected range (B5:B9), whilst text "yellow" and "blue" each appear twice. Therefore, this formula returns the value of "purple" as this is the least occurring text in the range. |
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return most frequently occurring text | How to return the most frequently occurring text | |
| Return most frequently occurring text if begins with specific value | How to get the most frequently occurring text which begins with specific a value |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions 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 | |
| MIN Function | The Excel MIN function returns the smallest value from a specified range of numeric values |