Why does FREQUENCY return more numbers than I put in bins?

FREQUENCY always returns length(bins_array) + 1 results. With bins {50; 100}, you get three counts: items ≤50, items between 50–100, and items >100. The extra count captures values exceeding your highest bin, which is essential for a complete distribution.

What happens if my bins array is not sorted?

FREQUENCY doesn't error, but the results become meaningless because the function assumes bins are in ascending order and calculates ranges accordingly. Always sort your bins from smallest to largest before using FREQUENCY.

Can I use FREQUENCY to count text values or categories?

No. FREQUENCY works exclusively with numeric data. To count text values (SKUs, warehouse names, categories), use COUNTIF (single condition) or COUNTIFS (multiple conditions) instead.

How do I display FREQUENCY results properly in a spreadsheet?

FREQUENCY returns a vertical array. In Google Sheets, select the starting cell and the array spills downward automatically. In Excel, select a vertical range the size of your expected result, type the formula, and press Ctrl+Shift+Enter to activate array mode.

FREQUENCY function

FREQUENCY returns an array of counts showing how many values in a dataset fall within specified bin thresholds.

=FREQUENCY(data_array, bins_array)

Generate a FREQUENCY formula

Describe what you need. The generator will reach for FREQUENCY where FREQUENCY is the right tool, and tell you when it is not.

Arguments

How FREQUENCY reads its arguments

Argument	Required	Description
data_array	Required	Range of numeric values to analyze. Non-numeric entries, including text and blanks, produce #VALUE!; data does not need to be sorted.
bins_array	Required	Array of numeric thresholds in ascending order that define bin boundaries. Non-numeric or unsorted entries produce #VALUE! or incorrect results.

Returns

A vertical array of numbers, with length equal to bins_array length plus one (representing values exceeding the highest bin).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Categorize inventory by stock level

SKU	On Hand
SKU001	45
SKU002	8
SKU003	156
SKU004	22
SKU005	72
SKU006	91
SKU007	5
SKU008	134

=FREQUENCY(C2:C9,{50;100})

Result: {4; 2; 2}

FREQUENCY tallies items with stock ≤50 (4 items), between 50–100 (2 items), and above 100 (2 items). This distribution highlights which products are understocked and need urgent reordering.

2. Group products by unit cost tier

SKU	Unit Cost
SKU001	12.5
SKU002	25
SKU003	8.75
SKU004	45
SKU005	15.5
SKU006	32
SKU007	18
SKU008	22.5

=FREQUENCY(D2:D9,{15;30})

Result: {2; 4; 2}

FREQUENCY divides the 8 products into price tiers: budget items ≤$15 (2 SKUs), mid-range $15–$30 (4 SKUs), and premium above $30 (2 SKUs). This segmentation supports procurement and pricing strategies.

3. Analyze stock surplus or shortage

SKU	On Hand	Reorder Pt	Surplus
SKU001	45	20	25
SKU002	8	15	-7
SKU003	156	50	106
SKU004	22	30	-8
SKU005	72	40	32
SKU006	91	35	56
SKU007	5	25	-20
SKU008	134	60	74

=FREQUENCY(C2:C9-D2:D9,{-5;50})

Result: {3; 2; 3}

By computing On Hand minus Reorder Point and binning the results, FREQUENCY categorizes stock health: understocked (3 SKUs below -5), adequate (2 SKUs in range), and overstocked (3 SKUs above 50). This immediately surfaces inventory imbalances.

Common errors

Which FREQUENCY error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	data_array contains text, dates, or non-numeric entries. FREQUENCY cannot count non-numeric values; SKU codes, warehouse names, or text mixed into a numeric column will trigger this error.	Isolate only numeric columns in data_array. For example, use only the On Hand or Cost column, not SKU or Warehouse. If your data includes blanks, verify they aren't being interpreted as text.
#VALUE!	bins_array contains text, dates, errors, or mixed data types. FREQUENCY requires numeric thresholds; entering bins like 'Low', 'Medium', 'High' instead of 50, 100, etc. will fail.	Replace text labels with numeric values. Use constant numbers ({50;100}) or references to numeric cells only. Avoid formulas that return errors or cells formatted as text.
#REF!	data_array or bins_array references a deleted column, removed sheet, or invalid range. If you delete a column that was part of the original formula range, the reference breaks.	Re-enter the formula with current valid ranges. Consider using named ranges (e.g., InventoryLevels) for stability, or use absolute references ($C$2:$C$9) to prevent accidental deletion impacts.

Tips and when to use something else

Bins must be sorted in ascending order; unsorted bins will not produce an error but will give incorrect frequency counts that do not represent your intended distribution.
FREQUENCY always returns one more element than bins_array—the final count includes all values exceeding the highest bin, which is useful for capturing 'out of range' items.
Use COUNTIF for a single threshold (e.g., 'count items > 50') or COUNTIFS for multiple independent conditions; reserve FREQUENCY for multi-bin distribution analysis.
In Excel, enter FREQUENCY as an array formula using Ctrl+Shift+Enter to display all results in a vertical range; Google Sheets enters it automatically and spills the array downward.

Related functions

COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.COUNTIFSCOUNTIFS counts rows where all criteria are met across multiple ranges, useful for filtering inventory and finding matches across conditions.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.

Frequently asked questions

Why does FREQUENCY return more numbers than I put in bins?: FREQUENCY always returns length(bins_array) + 1 results. With bins {50; 100}, you get three counts: items ≤50, items between 50–100, and items >100. The extra count captures values exceeding your highest bin, which is essential for a complete distribution.
What happens if my bins array is not sorted?: FREQUENCY doesn't error, but the results become meaningless because the function assumes bins are in ascending order and calculates ranges accordingly. Always sort your bins from smallest to largest before using FREQUENCY.
Can I use FREQUENCY to count text values or categories?: No. FREQUENCY works exclusively with numeric data. To count text values (SKUs, warehouse names, categories), use COUNTIF (single condition) or COUNTIFS (multiple conditions) instead.
How do I display FREQUENCY results properly in a spreadsheet?: FREQUENCY returns a vertical array. In Google Sheets, select the starting cell and the array spills downward automatically. In Excel, select a vertical range the size of your expected result, type the formula, and press Ctrl+Shift+Enter to activate array mode.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17