Excel Practice
Lessons Lesson 87

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

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

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

In E2, keep only the North amounts with FILTER: the amounts, and the test that the region equals North. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it.

To begin, type it exactly:

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

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

Every step

  1. 1

    In E2, keep only the North amounts with FILTER: the amounts, and the test that the region equals North. Excel would spill the whole list down the column from here. This grid does not spill, so it shows the first result only. The next steps wrap the array in a function that reads all of it. To begin, type it exactly: =FILTER(C2:C9,A2:A9="North").

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

  2. 2

    In E4, the North total: SUM around the same FILTER.

    Hint. SUM of the FILTER.

  3. 3

    In E6, how many North orders there are: ROWS around the FILTER.

    Hint. ROWS of the FILTER.

  4. 4

    In E8, the first product with an amount over 200. Show the text None if there is no such row, with FILTER's third argument.

    Hint. Products returned, amounts tested.

  5. 5

    In E10, write a formula using AVERAGE and FILTER that gives the average South amount.

    Hint. AVERAGE of the FILTER.

  6. 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.