The FILTER function in Excel

Q: How do I FILTER with multiple criteria?

Wrap each test in brackets and multiply them for AND: =FILTER(A2:D7,(B2:B7="Sam")*(D2:D7>3)). Add them with + for OR.

Q: How do I total or count the filtered results?

Wrap FILTER in another function: =SUM(FILTER(D2:D7,B2:B7="Sam")) adds Sam's hours, and =ROWS(FILTER(...)) counts the matching rows.

FILTER returns all the rows (or just the columns you choose) that meet a condition, and the results spill into the cells below automatically. Unlike the Filter button, it's a live formula, so the list updates as the data changes. Use it to build "show me every open task for Sam" views, exception lists and mini reports.

Syntax

=FILTER(array, include, [if_empty])

array	The data to return, like `A2:D7` for full rows or `A2:A7` for just one column.
include	A TRUE/FALSE test with one result per row, like `C2:C7="Blocked"`. Multiply tests for AND, add them for OR.
if_empty	Optional. What to show when nothing matches, like `"None"`. Without it you get #CALC!.

Try it yourself

This is a live spreadsheet. Type the formula and press Enter.

In F2, use FILTER to list the names of every candidate (A2:A7) whose stage is Interview. The results will spill down from F2.

	A	B	C	D	F
1	Candidate	Role	Stage	Score	At interview
2	Noor Aziz	Analyst	Interview	82
3	Ethan Brooks	Designer	Screening	74
4	Sara Lindqvist	Analyst	Offer	91
5	Kwame Mensah	Analyst	Interview	77
6	Julia Novak	Designer	Interview	88
7	Ravi Shah	Designer	Rejected	59
8
9
10

Tip: The first argument is the column of names. The second is a test on the Stage column.

Examples

=FILTER(A2:A7,C2:C7="Blocked")→ "Budget sign-off"

Lists every task whose status is Blocked. When several rows match, the names spill down one per cell.

=TEXTJOIN(", ",TRUE,FILTER(A2:A7,(B2:B7="Sam")*(C2:C7<>"Done")))→ "Site photos, Launch email"

Two conditions multiplied together act as AND: Sam's tasks that aren't done. TEXTJOIN then puts the list in a single cell.

=FILTER(A2:A7,D2:D7>20,"No large tasks")→ "No large tasks"

No task is over 20 hours, so FILTER shows the if_empty message instead of #CALC!.

Example data

1	Task	Owner	Status	Hours
2	Draft brief	Sam	Done	4
3	Vendor quotes	Priya	In progress	6
4	Site photos	Sam	In progress	3
5	Budget sign-off	Leo	Blocked	2
6	Press kit	Priya	Not started	8
7	Launch email	Sam	Not started	5

Common mistakes

#SPILL! error
FILTER needs empty cells below (and to the right of) the formula for its results. Clear anything in the way, or move the formula to an empty area.
Using AND() or OR() in include
AND and OR return one TRUE/FALSE for the whole range, not one per row. Use (test1)*(test2) for AND and (test1)+(test2) for OR.
Ranges of different heights
include must have exactly one row per row of array. FILTER(A2:D7,C2:C8="Done") returns #VALUE!.
#CALC! when nothing matches
An empty result is an error unless you give a fallback. Add a third argument such as "No matches".

FAQ

How do I FILTER with multiple criteria?+

Wrap each test in brackets and multiply them for AND: =FILTER(A2:D7,(B2:B7="Sam")*(D2:D7>3)). Add them with + for OR.

Which versions of Excel have FILTER?+

Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions show #NAME?.

How do I total or count the filtered results?+

Wrap FILTER in another function: =SUM(FILTER(D2:D7,B2:B7="Sam")) adds Sam's hours, and =ROWS(FILTER(...)) counts the matching rows.

Lesson

Master FILTER step by step: FILTER: pull matching rows with a formula

Return every row that meets one or more conditions with FILTER, and show a friendly message when nothing matches.

Related functions

XLOOKUP COUNTIFS TEXTJOIN