• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel Formula to Find the Least Frequent Value

Excel Formula to Find the Least Frequent Value

This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.

The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.

Return the least frequent value in a list

This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.

{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}

Excel Formula Explained

This formula is an array formula so you need to press Ctrl + Shift + Enter, and not Enter. This will put the curly braces around the formula. You do not type these.

Within this formula the COUNTIF functions are used to return how many times each name occurs in the list. The COUNTIF functions return the result below;

{2;5;5;4;4;5;4;4;5;2;4;5;4;4;4}

This means that the name in the first cell of that range (A2) occurs twice, 2nd cell (A3) occurs five times, 3rd cell (A4) occurs five times and so on.

The MIN function returns the smallest number from that array, which is 2 in this example.

The MATCH function is then used to search for the position of the first instance of 2 (the least mentioned names position). The result of this is 1, because the first instance of 2 is in the first cell of range A2:A16.

The INDEX function then returns the value which is in that cell (A2). Which in this example is Ross. Watch the video below for a visual explanation of this formula.

The INDEX and MATCH functions are awesome when used together for a flexible lookup formula. Find out more at this INDEX and MATCH tutorial.

Watch the Video

Seriously improve your Excel Formula skills with our online course. Over 100 formulas covered. Sign Up Now.

Related Posts:

  • Excel Formula to Find the Cell Address of a Value
    Excel Formula to Find the Cell Address of a Value
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Display Negative Time in Excel
    Display Negative Time in Excel
  • Advanced Excel Skills
    Advanced Excel Skills

Reader Interactions

Comments

  1. John Prouzos says

    12 December 2020 at 7:38 am

    Hello,
    I use Excel 2007. I used the

    Reply
  2. John Prouzos says

    12 December 2020 at 7:43 am

    I tried your formula =INDEX(A2:A1730,MATCH(MIN(COUNTIF(A2:A1730,A2:A1730)),(COUNTIF(A2:A1730,A2:A1730),0)) to find the least occurring number in a column of 1730 numbers. It worked the first time on Col A but when I tried it on the second column B (changing the A’s to B’s) it did not work. I did the control, shift, enter but nothing happened. Can you please tell me where I went wrong.
    Thank you.

    Reply
    • Alan Murray says

      14 December 2020 at 1:29 pm

      I am not sure what has gone wrong John. Do you get an error? Are there numbers in that column?
      It is probably a case of calculating the formula if you receive the same answer. Double click on the cell to edit the formula and do Ctrl + Shift + Enter.

      Reply
      • John Prouzos says

        31 January 2021 at 6:38 am

        Thanks, Alan.
        I think I copied the formula incorrectly. It works fine. Sorry for the late reply. I did not realise you had responded to my email.

        Reply
        • Alan Murray says

          31 January 2021 at 7:58 am

          No worries, glad it works.

          Reply
          • lance says

            11 December 2021 at 6:02 am

            What about excluding the number zero when the array/range has blanks

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·