Count the Occurrences of the Largest or Smallest Value in a Range

Download Project Files
Author:

How to count the occurrence of the largest or smallest value in a range; basically, how many times the MAX or MIN value occurs in a range.

Sections:

Max

Min

Notes

Max

=COUNTIF(A1:A6,MAX(A1:A6))

A1:A6 is the range with the values. Change that in both places in the formula to fit your data.

1ac374edb073410f11f4020f699fedfb.png

Result:

cb47988803c290de27a65bd1622d8887.png

The MAX() function gets the highest value from the range and then returns that to the COUNTIF() function, which uses that to determine which values in the range to count.

Min

=COUNTIF(A1:A6,MIN(A1:A6))

A1:A6 is the range with the values. Change that in both places in the formula to fit your data.

e1da0fa5b84b971985a9c5ed64e35f52.png

Result:

13a137aaf90d1dc6a8ded064a93b96f4.png

The MIN() function gets the lowest value from the range and then returns that to the COUNTIF() function, which uses that to determine which values in the range to count.

Notes

This is a simple combination of two functions in order to return the count; nothing more takes place here, just some function nesting.

Make sure to download the file for this tutorial to work with these examples in Excel.

Download the Project Files

Follow along with the tutorial by downloading the files used in it. (Completely Free)

Phone number is optional - we use it to send course discounts and weekly updates via SMS.

SMS Terms ▾
By submitting this form, you agree to receive recurring automated marketing text messages from TeachExcel at the mobile number provided. Consent is not a condition of any purchase. Msg frequency varies. Msg & data rates may apply. Reply HELP for help or STOP to cancel at any time.

View our Privacy Policy | Terms of Use

Excel Function: COUNTIF(), MAX(), MIN()
Excel Version: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016
Downloadable Files: Project File

Download Project File

Follow along with the tutorial by downloading the files used in it. (Completely Free)

Phone number is optional - we use it to send course discounts and weekly updates via SMS.

SMS Terms ▾
By submitting this form, you agree to receive recurring automated marketing text messages from TeachExcel at the mobile number provided. Consent is not a condition of any purchase. Msg frequency varies. Msg & data rates may apply. Reply HELP for help or STOP to cancel at any time.

View our Privacy Policy | Terms of Use

Question? Ask it in our Excel Forum

Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Input Form to Get Data and Store it in Another Tab in Excel
Tutorial: How to make a user input form in Excel and have the data stored on another worksheet at th...
test 1
: Code: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Conver...
test 2 ? 国
: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Convert HTML...
Macro to Replace HTML Entities with Readable Text
: Excel macro that converts all HTML entities into their readable equivalent. This includes ...
Get the Name of a Worksheet in Macros VBA in Excel
Tutorial: How to get the name of a worksheet in Excel using VBA and Macros and also how to store tha...
Find the Most Frequently Occurring Value in a List in Excel
Tutorial: Use a formula to return the most frequently occurring word or value from a range of cells...
Tutorial Details
Excel Function: COUNTIF(), MAX(), MIN()
Excel Version: Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016
Downloadable Files: Project File

Download Project File

Follow along with the tutorial by downloading the files used in it. (Completely Free)

Phone number is optional - we use it to send course discounts and weekly updates via SMS.

SMS Terms ▾
By submitting this form, you agree to receive recurring automated marketing text messages from TeachExcel at the mobile number provided. Consent is not a condition of any purchase. Msg frequency varies. Msg & data rates may apply. Reply HELP for help or STOP to cancel at any time.

View our Privacy Policy | Terms of Use
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course