FILTER

About this lesson

FILTER returns the rows of a range where a test is TRUE, as an array. Any aggregate wrapped round it, SUM, ROWS, AVERAGE or MAX, summarises just those rows.

By the end you can

keep only the rows that pass a test with FILTER.
sum, count or average just those rows.
say what FILTER returns when nothing passes.

Practises FILTER SUM ROWS SUMIF UNIQUE

By Ali, Founder, Excel Lessons

The idea

FILTER is the conditional functions made general. SUMIF answers one question about one condition. SUM(FILTER(…)) answers the same one and then any other. The test is a real comparison, A2:A9="North" or C2:C9>200, not a criteria string. And the returned column need not be the tested one.

Two details. The third argument is what to return when no row passes. Without it, an empty result is an error. And this grid shows the first surviving row. Excel 365 spills all of them, which is how a filtered list is built on a live sheet.

The mistake to watch for

Leaving out the third argument. When no row passes the test, FILTER returns an error. SUM or ROWS around it carries the error on. Give it what to return for an empty result. And the test range and the return range must be the same height, or the whole formula refuses.

Where this comes up again

The SUM Function Spreadsheet Basics
AVERAGE Everyday Math
Putting It Together Conditional Math
SUMPRODUCT: Weighted Totals and Conditions Summaries and Reports

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

FILTER takes what to keep, then the test.

In `E2`, type:

=FILTER(C2:C9,A2:A9="North")

Row	A	B	C	E	F	G
1	Region	Product	Amount	North amounts	Over 200	North total
2	North	Tee	80
3	South	Hoodie	240			North orders
4	North	Cap	60
5	East	Tee	120			South average
6	North	Hoodie	220
7	South	Cap	45
8	East	Hoodie	260
9	North	Tee	95

Every step

1

FILTER takes what to keep, then the test. In E2, type: =FILTER(C2:C9,A2:A9="North").

Hint. Amounts, then A2:A9="North".
2

In G2, total the North amounts with SUM around FILTER.

Hint. SUM of the FILTER.
3

In G4, count the North orders with ROWS around FILTER.

Hint. ROWS of the FILTER.
4

FILTER's third argument is what to show when nothing matches. In F2, list the products over 200, with "None" if there are none.

Hint. FILTER returns products, and tests the amounts.
5

In G6, find the average South amount. Put AVERAGE around FILTER.

Hint. AVERAGE of the FILTER.
6

One edge to watch. Read =ROWS(FILTER(C2:C9,C2:C9>=220)) and say how many rows pass with at-least instead of more-than.

Hint. Equal counts, this time.