Fix Google Sheets FILTER Error: 'FILTER has mismatched range sizes'

The Error

You write a FILTER formula, press Enter, and Sheets throws this back at you:

FILTER has mismatched range sizes. Expected row count: 100, column count: 1. Actual row count: 50, column count: 1.

The numbers change depending on your data. The problem doesn't: the range you're filtering and the condition range are two different sizes.

Root Cause

FILTER(range, condition1, [condition2, ...]) is strict about one thing — every condition array must have the exact same dimensions as the data range. If your data is A2:C100 (99 rows), every condition must also span 99 rows. No exceptions.

Four situations that trigger this almost every time:

The condition refers to a different sheet or named range that has fewer rows.
You hard-coded a range like D2:D50 but the data runs to row 100.
A helper column used as the condition was added later and only partially filled down.
Two computed arrays — say, from nested ARRAYFORMULA calls — return different lengths.

Fix It: Step-by-Step

Step 1 — Confirm the mismatch with ROWS()

Before touching anything, verify you actually have a size mismatch. Drop these two formulas into empty cells:

=ROWS(A2:A100)   // data range
=ROWS(D2:D50)    // condition range

If they return different numbers — say, 99 vs 49 — that's your culprit.

Step 2 — Make both ranges the same explicit size

The direct fix: extend the condition range to match the data range.

Before (broken):

=FILTER(A2:C100, D2:D50="Yes")

After (fixed):

=FILTER(A2:C100, D2:D100="Yes")

Both ranges now cover rows 2–100. That's 99 rows each. Sizes match, error gone.

Step 3 — Switch to open-ended ranges

Hard-coding the last row is fragile. Your data grows; the formula doesn't — and the error comes back. Open-ended ranges solve this permanently:

=FILTER(A2:C, D2:D="Yes")

Drop the row number and Sheets extends both ranges to wherever your data ends. A2:C and D2:D will always be the same length, no matter how many rows you add.

Step 4 — Fix conditions built from formulas

Sometimes your condition isn't a direct cell reference — it's a computed array. Both arrays still have to return the same number of rows.

Broken — arrays return different lengths:

=FILTER(A2:C, ARRAYFORMULA(LEN(B2:B50)>3))

Fixed — keep ranges consistent:

=FILTER(A2:C, ARRAYFORMULA(LEN(B2:B)>3))

The fix is the same idea: open-ended range on the condition side.

Step 5 — Stacking multiple conditions

Every condition in a multi-condition FILTER must match the data range. Miss one and the error fires.

=FILTER(A2:C, D2:D="Yes", E2:E>100, F2:F<>"")

If E2:E covers only 80 rows while the data covers 99, that's enough to break it. Run =ROWS() on each condition separately to track down which one is off.

Step 6 — Cross-sheet references

Pulling a condition from another sheet is one of the sneakier causes. Sheet2 might have a different number of rows than your current sheet — and you won't notice until the error appears.

// Wrong: Sheet2 only has 50 rows of status data
=FILTER(A2:C100, Sheet2!B2:B50="Active")

// Fixed: open-ended on both sides
=FILTER(A2:C, Sheet2!B2:B="Active")

Quick Diagnostic Checklist

Run =ROWS(your_data_range) and =ROWS(your_condition_range) — do the numbers match?
Any helper columns used as conditions? Check they're filled all the way to the last data row.
Using a named range as a condition? Verify its actual size under Data → Named ranges.
Is one range pulling from another sheet with a different row count?
Nesting ARRAYFORMULA, MAP, or REDUCE inside the condition? Test the formula in isolation first to confirm the output size.

Prevention

A few consistent habits cut this error rate to near zero:

Default to open-ended ranges (A2:A, not A2:A100) in both data and condition arguments.
When adding a helper column as a condition, fill the entire column before wiring it into FILTER.
Test any ARRAYFORMULA-based condition in a separate cell first. Confirm it returns the row count you expect before using it as a condition.
Any time you mix ranges from different sheets or named ranges, run a quick =ROWS() check on both sides before committing the formula.

Verify the Fix

Three quick checks after updating the formula:

The cell should show filtered results — not the error.
Sanity-check the count: =ROWS(FILTER(A2:C, D2:D="Yes")) should return a number that makes sense for your data.
Add a new row at the bottom and confirm the filter still picks it up. If it does, your open-ended range is working correctly.