How to Use the FREQUENCY Function in Google Sheets to Calculate Distribution Bins

When analysing numerical datasets—such as exam scores, survey ratings, customer ages, or response times—summarising raw numbers into frequency distributions is a crucial first step toward creating histograms and statistical profiles. While you could create repetitive COUNTIFS formulas for every individual threshold, Google Sheets provides the native FREQUENCY function. As an automated dynamic array function, FREQUENCY populates an entire vertical column of bin counts with a single formula.

Understanding the Syntax of FREQUENCY

The FREQUENCY function takes two arguments:

=FREQUENCY(data, bins)

Argument	Type	Description
data	Required	The range or array containing the raw numerical values you wish to count and categorize.
bins	Required	A column range containing the upper thresholds (boundaries) that define each bucket or bin. Bins should be sorted in ascending order.

Crucial Rule: The FREQUENCY function always returns an array that is one element larger than the bins array. The final extra cell captures all data values that exceed the highest specified bin threshold.

How the Bin Thresholds Work

Each value in the bins parameter represents an inclusive upper bound for that bucket:

• The first bin counts all values where value <= bin_1.
• The second bin counts values where bin_1 < value <= bin_2.
• The nth bin counts values where bin_(n-1) < value <= bin_n.
• The trailing overflow bin counts any remaining values where value > bin_max.

Step-by-Step Example: Calculating Student Score Distributions

Suppose you have test scores in column A (cells A2:A25) and you want to group them into four performance grade bands:

1. Set up your bin boundaries in an adjacent column (e.g., column C):
   • Cell C2: 59 (Failing band: scores up to 59)
   • Cell C3: 69 (Pass band: scores 60 to 69)
   • Cell C4: 79 (Average band: scores 70 to 79)
   • Cell C5: 89 (Good band: scores 80 to 89)
2. Click on the cell where you want your frequency counts to output (e.g., cell D2).
3. Enter the formula:

   =FREQUENCY(A2:A25, C2:C5)

4. Press Enter. Google Sheets automatically spills the counts down cells D2:D6.
5. Cell D6 automatically reflects all scores equal to 90 or above (the overflow bin).

FREQUENCY vs COUNTIFS: Which Should You Use?

Understanding when to choose FREQUENCY over COUNTIFS streamlines spreadsheet architecture:

Feature	FREQUENCY	COUNTIFS
Formula Overhead	A single dynamic array formula spills all bucket values.	Requires a unique manual formula copied across every row.
Performance	Optimised single-pass internal algorithm, ultra-fast on massive datasets.	Re-scans the source range multiple times for each condition.
Non-Numeric Handling	Blanks and text strings are automatically ignored.	Requires explicit filtering criteria to bypass text or blank entries.
Multiple Criteria	Limited to single-column continuous numerical binning.	Can evaluate multiple arbitrary criteria simultaneously.

Pairing FREQUENCY with Charts and Data Visualisation

Once your frequency counts spill into your sheet, highlight both the bin labels and frequency values, click Insert > Chart, and select a Column Chart or Bar Chart. This instantly yields an accurate, dynamic histogram that updates whenever raw records in your source dataset change.