Syntax, a live playground, a measured conditional gallery, AND/OR criteria, the #CALC! trap, and a sourced error library: the dynamic-array FILTER function.
Returns the rows of array where include is TRUE, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.
FILTER returns the rows that meet a condition and spills them. The live gallery wraps FILTER in SUM/COUNTA/INDEX so each result is one measurable value. Every row was measured live in HyperFormula. Click any row to load it.
| Expression | Result | Why |
|---|---|---|
| =SUM(FILTER(C2:C8,B2:B8="East")) | 900 | Total sales for the East region |
| =COUNTA(FILTER(A2:A8,B2:B8="East")) | 4 | How many East rows |
| =SUM(FILTER(C2:C8,C2:C8>200)) | 950 | Sales above 200 |
| =INDEX(FILTER(A2:A8,B2:B8="East"),1) | Alice | First East name |
| =FILTER(A2:A8,B2:B8="East") | Alice | Top-left of the spill (Excel shows all 4) |
| =COUNTA(FILTER(A2:A8,C2:C8>=250)) | 3 | Rows with sales at least 250 |
| =IFERROR(SUM(FILTER(C2:C8,B2:B8="Nope")),0) | 0 | No match guarded to 0 |
| =COUNTA(FILTER(A2:A8,(B2:B8="East")*(C2:C8>200))) | 2 | East AND sales>200 |
| Expression | Result | Why |
|---|---|---|
| =FILTER(A2:A8,(B2:B8="East")*(C2:C8>200)) | 2 rows | East AND sales>200 (Bob, Dave) |
| =FILTER(A2:A8,(B2:B8="East")+(B2:B8="West")) | 7 rows | East OR West (every row) |
| =FILTER(A2:C8,B2:B8="East") | 4 rows | Return whole rows, not one column |
Edit the table or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to total a region, count matching rows, and combine AND/OR criteria.
8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.
FILTER requires dynamic arrays: Excel 365 / 2021, Excel Online and Google Sheets. Excel 2019 and earlier return #NAME?; use SUMIFS/COUNTIFS there.
| Platform | Support | Notes | Gotcha |
|---|
FILTER is one piece. The full course walks the toolkit end to end with live, measured examples.