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

Add to Favorites
Author: don

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.

Question? Ask it in our Excel Forum


Excel Function: COUNTIF(), MAX(), MIN()
Downloadable Files: Excel File

Similar Content on TeachExcel
Excel VBA MsgBox - Message Box Macro
Tutorial: Create a pop-up message box in Excel using VBA Macros. This allows you to show a message t...
test 1
: Code: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Conver...
test 2 🙈 国
: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Convert HTML...
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...
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...
Tutorial Details
Excel Function: COUNTIF(), MAX(), MIN()
Downloadable Files: Excel File
Similar Content
Excel VBA MsgBox - Message Box Macro
Tutorial: Create a pop-up message box in Excel using VBA Macros. This allows you to show a message t...
test 1
: Code: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Conver...
test 2 🙈 国
: Sub Convert_Html_Entities() ''''''''''''''  TeachExcel.com  '''''''''''''' 'Convert HTML...
Excel Forum