FREQUENCY Function in Google Sheets: Complete Guide

Key Insights

FREQUENCY returns an array with n+1 elements for n bins, where the extra element counts values exceeding your highest bin boundary
Unlike COUNTIF-based alternatives, FREQUENCY handles bin ranges automatically and processes the entire distribution in a single formula
The function uses inclusive upper boundaries (≤), meaning a value exactly matching a bin edge falls into that bin, not the next one

Introduction to FREQUENCY

FREQUENCY is one of Google Sheets’ most underutilized statistical functions. It counts how many values from a dataset fall within specified ranges—called bins or classes—and returns the complete distribution in one operation. If you’ve ever built a histogram manually using nested COUNTIF formulas, FREQUENCY does that work in a single function call.

The practical applications are everywhere: grading student scores, analyzing age demographics, categorizing sales performance, understanding response time distributions, or any scenario where you need to group continuous data into discrete categories. FREQUENCY transforms raw numbers into actionable distribution insights.

What makes FREQUENCY particularly powerful is its array-native design. Rather than calculating one bin at a time, it processes your entire dataset against all bin boundaries simultaneously. This makes it faster, cleaner, and less error-prone than the alternatives.

Syntax and Parameters

The FREQUENCY function takes two arguments:

=FREQUENCY(data, classes)

data: The range containing the values you want to count. This is your raw dataset—test scores, ages, sales figures, or any numeric values.

classes: The range containing your bin boundaries (also called class intervals or breakpoints). These define where one category ends and the next begins.

Here’s a basic example:

Data (A1:A10): 15, 22, 35, 42, 48, 55, 61, 73, 88, 95

Bins (C1:C4): 25, 50, 75, 100

Formula: =FREQUENCY(A1:A10, C1:C4)

Output:
2   (values ≤ 25: 15, 22)
3   (values > 25 and ≤ 50: 35, 42, 48)
2   (values > 50 and ≤ 75: 55, 61, 73)
2   (values > 75 and ≤ 100: 88, 95)
1   (values > 100: none, so 0... wait, that's 0)

The critical detail: FREQUENCY always returns one more element than the number of bins. With 4 bin boundaries, you get 5 output values. That final element counts anything exceeding your highest bin.

Understanding Bins and Output Arrays

The bin logic trips up most new users. Here’s exactly how FREQUENCY interprets your class boundaries:

Bins: 25, 50, 75, 100

Output[1]: count of values ≤ 25
Output[2]: count of values > 25 AND ≤ 50
Output[3]: count of values > 50 AND ≤ 75
Output[4]: count of values > 75 AND ≤ 100
Output[5]: count of values > 100

The boundaries are inclusive on the upper end. A value of exactly 50 falls into the “≤ 50” bin, not the “> 50” bin.

Let’s trace through a concrete example:

Data: 10, 25, 25, 30, 50, 51, 75, 100, 101

Bins: 25, 50, 75, 100

Bin breakdown:
≤ 25:        10, 25, 25     → 3 values
> 25, ≤ 50:  30, 50         → 2 values
> 50, ≤ 75:  51, 75         → 2 values
> 75, ≤ 100: 100            → 1 value
> 100:       101            → 1 value

FREQUENCY output: {3; 2; 2; 1; 1}

Notice that both instances of 25 fall into the first bin (≤ 25), and 50 falls into the second bin (≤ 50). The value 101 exceeds all bins and lands in the overflow category.

Basic Usage Examples

Grade Distribution

Suppose you have student scores in column A and want to count how many fall into each letter grade:

Scores (A2:A30): Various scores from 0-100

Grade bins (C2:C5): 60, 70, 80, 90

Formula in D2: =FREQUENCY(A2:A30, C2:C5)

Output interpretation:
D2: F grades (≤ 60)
D3: D grades (61-70)
D4: C grades (71-80)
D5: B grades (81-90)
D6: A grades (91-100, or > 90)

To add labels, put your grade letters in column E:

E2: F
E3: D
E4: C
E5: B
E6: A

Age Group Analysis

Grouping survey respondents by decade:

Ages (A2:A100): Various ages

Decade bins (C2:C6): 20, 30, 40, 50, 60

Formula: =FREQUENCY(A2:A100, C2:C6)

Output:
Row 1: Ages 20 and under
Row 2: Ages 21-30
Row 3: Ages 31-40
Row 4: Ages 41-50
Row 5: Ages 51-60
Row 6: Ages over 60

Sales Performance Tiers

Categorizing sales reps by quarterly performance:

Sales figures (B2:B50): Dollar amounts

Tier boundaries: 10000, 25000, 50000, 100000

Formula: =FREQUENCY(B2:B50, {10000, 25000, 50000, 100000})

Output:
Tier 1 (≤ $10K): struggling
Tier 2 ($10K-$25K): developing
Tier 3 ($25K-$50K): performing
Tier 4 ($50K-$100K): exceeding
Tier 5 (> $100K): top performers

Note the inline array syntax using curly braces—you don’t need a separate range for bins.

Array Formula Behavior and Spill

FREQUENCY is an array function, meaning it returns multiple values from a single formula. In modern Google Sheets, this happens automatically through “spill” behavior.

When you enter a FREQUENCY formula in a cell:

Cell D2: =FREQUENCY(A2:A30, C2:C5)

The formula occupies D2, but the results automatically spill into D3, D4, D5, and D6. You’ll see the formula in D2, but the other cells display results with a slightly grayed appearance indicating they’re part of the spill range.

Important considerations:

Don’t put data in spill cells: If D3 already contains something, FREQUENCY throws a #REF! error. Clear the cells below your formula.
Legacy behavior: Older spreadsheets or certain contexts may require Ctrl+Shift+Enter to enter array formulas. Modern Sheets handles this automatically.
Referencing spill results: You can reference individual elements using INDEX:

=INDEX(FREQUENCY(A2:A30, C2:C5), 3)  // Gets the third bin count

Wrapping in other functions: When combining FREQUENCY with functions like SUM or MAX, the array behavior persists:

=SUM(FREQUENCY(A2:A30, C2:C5))  // Should equal COUNT of your data
=MAX(FREQUENCY(A2:A30, C2:C5))  // Finds the most populated bin

Combining FREQUENCY with Other Functions

Inline Histogram with SPARKLINE

Create a visual distribution directly in a cell:

=SPARKLINE(FREQUENCY(A2:A100, B2:B10), {"charttype","bar"})

This renders a tiny bar chart showing your frequency distribution. Perfect for dashboard summaries.

Dynamic Bins with UNIQUE and SORT

If your bins should adapt to the data:

=FREQUENCY(A2:A100, SORT(UNIQUE(A2:A100)))

This counts occurrences of each unique value—essentially a dynamic frequency table.

Labeled Output with Array Manipulation

Combine bin labels with counts:

Bins in C2:C5, Labels in B2:B6

={B2:B6, FREQUENCY(A2:A100, C2:C5)}

This creates a two-column output with labels and their corresponding counts.

Filtered Frequency with FILTER

Count frequency only for values meeting a condition:

=FREQUENCY(FILTER(A2:A100, B2:B100="Active"), C2:C5)

This calculates the distribution for only “Active” records.

Percentage Distribution

Convert counts to percentages:

=FREQUENCY(A2:A100, C2:C5) / COUNT(A2:A100) * 100

Or use ARRAYFORMULA for explicit array handling:

=ARRAYFORMULA(FREQUENCY(A2:A100, C2:C5) / COUNT(A2:A100) * 100)

Common Errors and Troubleshooting

#VALUE! Error

Cause: Text values in your data range or bins.

Fix: Clean your data with VALUE() or filter out text:

// Before (fails if A5 contains "N/A"):
=FREQUENCY(A2:A100, C2:C5)

// After:
=FREQUENCY(FILTER(A2:A100, ISNUMBER(A2:A100)), C2:C5)

All Zeros Except Last Bin

Cause: Bins not sorted in ascending order.

Fix: Sort your bins or wrap in SORT():

// Before (bins: 75, 25, 50, 100):
=FREQUENCY(A2:A100, C2:C5)  // Produces unexpected results

// After:
=FREQUENCY(A2:A100, SORT(C2:C5))

Missing the Overflow Count

Cause: Forgetting FREQUENCY returns n+1 elements.

Fix: Always leave room for one extra row below your formula, or explicitly handle it:

// If you only want counts within bins (ignoring overflow):
=ARRAY_CONSTRAIN(FREQUENCY(A2:A100, C2:C5), 4, 1)

#REF! Error on Entry

Cause: Cells in the spill range already contain data.

Fix: Clear all cells below your formula entry point before entering the FREQUENCY formula.

Unexpected Bin Assignments

Cause: Misunderstanding inclusive boundaries.

Fix: Remember that bin values are upper limits (≤). If you want 1-10, 11-20, 21-30, your bins should be 10, 20, 30—not 1, 11, 21.

// Wrong approach for "1-10, 11-20, 21-30":
Bins: 1, 11, 21  // This creates ≤1, 2-11, 12-21, >21

// Correct approach:
Bins: 10, 20, 30  // This creates ≤10, 11-20, 21-30, >30

FREQUENCY is a workhorse function that eliminates the tedious alternative of writing multiple COUNTIFS. Master the bin logic and array behavior, and you’ll find yourself reaching for it whenever distribution analysis comes up.