The FREQUENCY function calculates how often specified values occur within a range of values, and then returns a vertical array of numbers.

Syntax

=FREQUENCY(data_array,bins_array)

Arguments

Argument Description
data_array The original array, or reference to a set of values, for which you want to count frequencies
bins_array An array, or reference to intervals, into which you want to group the values in data_array

Note: Because FREQUENCY returns an array, it must be entered as an array formula.

  • The number of elements in the returned array is one more than the number of elements in bins_array. For example, when counting three ranges of values that are entered into three cells, be sure to enter FREQUENCY into four cells for the results.
  • FREQUENCY ignores blank cells and text.

Examples

  A B C D E
1 Ages Bins Frequency Formula Notes
2 7 5 4 {=FREQUENCY(A2:A10,B2:B3)} Number of children less than or equal to age 4
3 2 8 2   Number of children older than age 4 and less than or equal to age 8
4 8   3   Number of children older than age 9 or older
5 5        
6 12        
7 3        
8 11        
9 2        
10 10        

Usage note: Use FREQUENCY to count the number of children that fall within defined age groups.

  • In the example above, the FREQUENCY function entered into cells C2:C4 has been used to count the number of children falling into three different age ranges
  • The bins_array values specify the maximum values for the first two age ranges, i.e. the ages are split into the ranges 0-5 years, 6-8 years and 9 years+

Note: The curly brackets, { and }, seen in the formula entered in D2 are not entered by the user. Excel applies these to show the formula has been input as an array formula.

Common Function Error(s)

Problem What went wrong
#N/A Occurs if the array formula is entered into a range of cells that is too large, i.e. the #N/A error appears in all cells after the n’th cell, where n is the length of the bins_array + 1