Excel track · Exercise 80 of 86 Paid
FILTER: the rows that meet a condition
Add the rows that meet a condition without filtering the sheet first.
Advanced
Order status board
Read-only data · answer in B8
In B8, total the units of the orders whose status is Open, with FILTER choosing the rows and SUM adding them.
| A | B | C | |
|---|---|---|---|
| 1 | Order | Status | Units |
| 2 | SO-1 | Open | 40 |
| 3 | SO-2 | Shipped | 90 |
| 4 | SO-3 | Open | 70 |
| 5 | SO-4 | Shipped | 25 |
| 6 | SO-5 | Open | 60 |
| 7 | SO-6 | Shipped | 12 |
| 8 | Open units |
Reference answer
What it does
FILTER takes a block of values and a column of TRUE and FALSE tests of the same height. It returns only the rows where the test came back TRUE. The include argument B2:B7="Open" is TRUE for three orders, so FILTER hands back units of 40, 70 and 60.
Adding those three is the exercise: =SUM(FILTER(C2:C7,B2:B7="Open")) returns 170 in B8. Shipped orders come to 127 and all six orders to 297, so a formula that picks the wrong status still looks like a working formula. The status is typed inside the formula, so changing Open to Shipped re-answers the question without touching the data.
The optional third argument is the value to return when nothing matches. Leave it out and a status no order carries reports #N/A rather than an empty total.
Syntax
=FILTER(array, include, [if_empty])Worked example
On Order status board, =SUM(FILTER(C2:C7,B2:B7="Open")) returns 170.
=SUM(FILTER(C2:C7,B2:B7="Open"))Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | No order carries that status and no if_empty value was given, so there is nothing to add. |
#VALUE! | The include argument is text or the wrong height, usually a comparison that lost its equals sign or its quotes. |
#NAME? | FILTER arrived with dynamic arrays, so a workbook opened in an older Excel cannot run it. |
At work
An open ticket count that keeps up as rows arrive, and unbilled invoices asked for one client at a time.