✕
0 / 120

Excel FILTER function

FILTER returns the rows of a range that meet one or more conditions, and the list spills into the cells below.

Sales wants the open orders for one region, and the region is typed in H1 so it can be changed. In G4, write one formula that lists the order, customer and amount of every order from that region whose status is Open. When a region has no open orders, the formula shows None.

FILTER(array, include, [if_empty]) keeps the rows of the array where include is TRUE. A condition compares a whole column, like D2:D15=H1, and two conditions multiplied together both have to hold. The third argument is what to show when no row matches.

98 Rows that match FILTER Advanced

orders.xlsx
Ready

Click a cell while typing to insert it, drag to select a range, F4 adds $, Esc cancels.

Tap a cell while writing a formula to insert it, or slide across cells for a range. The strip above the keyboard has = ( ) : , $.

The FILTER guide

=FILTER(A2:C15, (D2:D15=H1)*(E2:E15="Open"), "None") returns every row of A2:C15 where both conditions hold, in the order they appear, and spills them into the cells below. It works in Excel 2021, Excel for Microsoft 365 and Excel for the web.

AdvancedAbout 3 minutesBy Updated

The syntax

=FILTER(array, include, [if_empty])

Like UNIQUE, FILTER spills: write it in one cell and the result fills the cells below and to the right. If one of them isn't empty, you get #SPILL!. See UNIQUE and #SPILL!.

Two conditions: * and +

Multiply two conditions for AND: (D2:D15=H1)*(E2:E15="Open") is 1 only on rows where both are TRUE. Add them for OR: (D2:D15="North")+(D2:D15="South") keeps a row when either is TRUE. Put each condition in brackets.

The functions AND() and OR() don't work here. They boil the whole range down to one TRUE or FALSE, so FILTER gets a single value instead of one per row and returns #VALUE!.

#CALC! and #VALUE!

ErrorCauseFix
#CALC!No row matched, and there's no third argument. Excel can't show an empty list.Add if_empty: "None" or "".
#VALUE!include isn't as tall as the array (D2:D14 against A2:C15), or AND() inside include.Use the same rows everywhere; use * and +.
#SPILL!Something is in the cells the result needs.Clear them.

Put the criterion in a cell

Compare with a cell, D2:D15=H1, instead of typing "North" into the formula. Change H1 and the list follows. That's also why if_empty matters: the formula works for North today and has to keep working for a region with nothing open.

The comparison ignores capitals, so "north" matches "North". It doesn't ignore spaces: "North " with a space at the end won't match. Clean the column with TRIM first.

Common mistakes

To sort the result, wrap it: =SORT(FILTER(…), 3, -1). See SORT and UNIQUE. To add the matches up instead of listing them, SUMIFS is simpler.

FAQ

How do I filter with two conditions in Excel?
Multiply the conditions inside FILTER: =FILTER(A2:C15,(D2:D15="North")*(E2:E15="Open")). Each condition goes in brackets. For either condition, add them with + instead.
Why does FILTER return #CALC!?
No row matched and the formula has no third argument. Add one, such as =FILTER(A2:C15,D2:D15=H1,"None"), and that text shows instead.
Why does FILTER return #VALUE!?
The include range isn't the same height as the array, or AND() or OR() was used inside it. Make every range cover the same rows and use * for AND, + for OR.
Which versions of Excel have FILTER?
Excel 2021, Excel 2024, Excel for Microsoft 365 and Excel for the web. In older versions, use an AutoFilter or SUMIFS and COUNTIFS for totals.

All challenges

  1. 01Starter, 3 min Your first formula= + - * /
  2. 02Starter, 3 min Add it upSUM
  3. 03Starter, 3 min AutoSum in one keystrokeAutoSum
  4. 04Starter, 3 min Find the averageAVERAGE
  5. 05Starter, 3 min Smallest and largestMIN, MAX
  6. 06Starter, 3 min Brackets change the answerOrder of operations
  7. 07Starter, 3 min Count what's thereCOUNT, COUNTA, COUNTBLANK
  8. 08Starter, 3 min Write once, fill downRelative references
  9. 09Starter, 3 min Fill rightCtrl + R
  10. 10Core, 3 min Lock it with $Absolute references
  11. 11Starter, 3 min Percent of a totalPercentages
  12. 12Starter, 3 min Make the numbers readableNumber formats
  13. 13Starter, 3 min Bold, italic, underlineCtrl + B, I, U
  14. 14Core, 3 min Up or down by how much?Percent change
  15. 15Core, 3 min What you see vs what's storedROUND
  16. 16Core, 3 min The typical valueMEDIAN
  17. 17Core, 3 min The most common valueMODE
  18. 18Core, 3 min Top 3 and bottom 3LARGE, SMALL
  19. 19Core, 3 min Always round up (or down)ROUNDUP, ROUNDDOWN
  20. 20Core, 3 min Round to the nearest 5, 15, 0.25CEILING, FLOOR, MROUND
  21. 21Core, 3 min Round to odd or evenEVEN, ODD
  22. 22Starter, 3 min How many days are left?TODAY
  23. 23Core, 3 min Select to the end in one moveCtrl + Shift + arrow
  24. 24Starter, 3 min Every cell has an addressCell addresses
  25. 25Starter, 3 min Select a rangeDrag, Shift + click
  26. 26Starter, 3 min Move without the mouseArrow keys, Enter, Tab
  27. 27Starter, 3 min Jump to the edge of the dataCtrl + arrow
  28. 28Starter, 3 min Whole rows and columnsShift + Space, Ctrl + Space
  29. 29Starter, 3 min Select everythingCtrl + A
  30. 30Starter, 3 min Let the status bar do the mathStatus bar: Sum, Count, Average
  31. 31Starter, 3 min Sort without breaking rowsSort
  32. 32Core, 3 min Sort by two columnsMulti-level sort
  33. 33Starter, 3 min Filter to what mattersFilter
  34. 34Core, 3 min Insert and delete rows fastInsert and delete rows
  35. 35Starter, 3 min Copy and paste a formulaCtrl+C, Ctrl+V
  36. 36Core, 3 min Paste values onlyPaste Special → Values
  37. 37Core, 3 min Highlight what's over targetConditional formatting
  38. 38Core, 3 min Spot the duplicatesDuplicate Values rule
  39. 39Starter, 3 min TRUE or FALSE?Comparisons
  40. 40Starter, 3 min Work across sheetsSheet tabs
  41. 41Core, 3 min Pass or fail?IF
  42. 42Core, 3 min Both or either?AND, OR
  43. 43Core, 3 min Exactly one of the twoXOR
  44. 44Core, 3 min Grade bands with nested IFNested IF
  45. 45Core, 3 min Many conditions, no nestingIFS
  46. 46Core, 3 min Match a code with SWITCHSWITCH
  47. 47Core, 3 min IF with AND and ORIF + AND, OR
  48. 48Core, 3 min What's in this cell?ISNUMBER, ISTEXT, ISBLANK
  49. 49Core, 3 min Count the ones that matchCOUNTIF
  50. 50Core, 3 min Add up the ones that matchSUMIF
  51. 51Core, 3 min Use a cell as the conditionCriteria in a cell
  52. 52Core, 3 min Partial matches with * and ?SUMIF, COUNTIF with wildcards
  53. 53Core, 3 min Count with two conditionsCOUNTIFS
  54. 54Core, 3 min Add up with two conditionsSUMIFS
  55. 55Core, 3 min Average with conditionsAVERAGEIF, AVERAGEIFS
  56. 56Core, 3 min Count the blanksCOUNTBLANK, COUNTA
  57. 57Core, 3 min Look it upVLOOKUP
  58. 58Core, 3 min Find the right bracketVLOOKUP approximate match
  59. 59Core, 3 min Fill a lookup downVLOOKUP + $
  60. 60Core, 3 min Look across a rowHLOOKUP
  61. 61Core, 3 min XLOOKUP, the modern wayXLOOKUP
  62. 62Starter, 3 min Fix #NAME?#NAME?
  63. 63Core, 3 min Where is it in the list?MATCH
  64. 64Core, 3 min Give me the nth oneINDEX
  65. 65Starter, 3 min Fix the capitalsUPPER, LOWER, PROPER
  66. 66Starter, 3 min First and last charactersLEFT, RIGHT
  67. 67Advanced, 3 min INDEX and MATCH togetherINDEX + MATCH
  68. 68Advanced, 3 min XLOOKUP with wildcardsXLOOKUP match_mode
  69. 69Advanced, 3 min Row and column lookupINDEX + MATCH + MATCH
  70. 70Advanced, 3 min Lock just the row or the columnMixed references
  71. 71Core, 3 min Mistake hunt: the totals don't add upFixing SUM
  72. 72Advanced, 3 min Growth per year (CAGR)CAGR with ^
  73. 73Advanced, 3 min Whole parts and leftoversINT, MOD
  74. 74Core, 3 min Use a rate from another sheetReferences to other sheets
  75. 75Advanced, 3 min Custom number formatsCustom formats
  76. 76Core, 3 min Catch the error, not the bugIFERROR
  77. 77Core, 3 min Mistake hunt: the commission sheetFixing IF
  78. 78Core, 3 min Which invoices are missing?Compare two lists
  79. 79Core, 3 min Mistake hunt: the regional reportFixing SUMIF
  80. 80Core, 3 min Reconcile the ledger with the bankXLOOKUP or VLOOKUP + IFNA
  81. 81Core, 3 min Join text together&, CONCAT, PROPER
  82. 82Core, 3 min Join a list with commasTEXTJOIN
  83. 83Core, 3 min Pull from the middleMID
  84. 84Core, 3 min FIND or SEARCH?FIND, SEARCH
  85. 85Core, 3 min Split first and last namesLEFT, MID + FIND
  86. 86Core, 3 min Stray spaces that break a countTRIM
  87. 87Core, 3 min Flag the bad entriesTRIM, LEN, EXACT
  88. 88Advanced, 3 min Clean a bank descriptionTEXTBEFORE, TEXTAFTER, PROPER
  89. 89Core, 3 min Swap one word for anotherSUBSTITUTE
  90. 90Core, 3 min Mistake hunt: #N/A everywhereFixing #N/A
  91. 91Core, 3 min Mistake hunt: hard-coded numbersReferences instead of typed numbers
  92. 92Core, 3 min Mistake hunt: numbers stored as textNumbers stored as text
  93. 93Core, 3 min Fix #DIV/0!#DIV/0!
  94. 94Core, 3 min Fix #VALUE!#VALUE!
  95. 95Core, 3 min Fix #REF!#REF!
  96. 96Core, 3 min Fix a circular referenceCircular references
  97. 97Core, 3 min One of each, then the totalsUNIQUE
  98. 98Advanced, 3 min Rows that matchFILTER
  99. 99Advanced, 3 min Sorted, unique list in one formulaSORT + UNIQUE
  100. 100Core, 3 min Your first pivot tablePivot tables
  101. 101Advanced, 3 min Slice the pivotPivot table filters
  102. 102Advanced, 3 min Which category went up most?SUMIFS, change, lookup
  103. 103Core, 3 min How many days late?Date arithmetic
  104. 104Core, 3 min Turn text into real datesDATEVALUE
  105. 105Core, 3 min Build a date from partsDATE, YEAR, MONTH
  106. 106Advanced, 3 min Month ends and working daysEOMONTH, NETWORKDAYS
  107. 107Core, 3 min Renewal datesEDATE
  108. 108Advanced, 3 min Totals between two datesSUMIFS with dates
  109. 109Core, 3 min Days overdue and aging bandsDate difference, aging bands
  110. 110Core, 3 min Age and years of serviceDATEDIF
  111. 111Core, 3 min How many late orders?Pick the tool
  112. 112Core, 3 min Sales per repPick the tool
  113. 113Core, 3 min Whose part is it?Pick the tool
  114. 114Core, 3 min Who gets the bonus?Pick the tool
  115. 115Core, 3 min Each region's sharePick the tool
  116. 116Advanced, 3 min Case: the supplier reportCase, five steps
  117. 117Advanced, 3 min Case: the monthly budgetCase, five steps
  118. 118Advanced, 3 min Case: the reorder listCase, five steps
  119. 119Advanced, 3 min Case: timesheet and overtimeCase, five steps
  120. 120Advanced, 3 min Case: the messy customer listCase, five steps

The whole course, in order.

The challenges teach one move each. The course puts them in order and makes you do each one twice, the second time on data you haven't seen.

$29 One time. No subscription. Early-access price. Get Pro for $29

Secure checkout on Stripe.

  • CourseAll 120 ready lessons in order, plus every one we add. The first 10 are free. Each formula lesson ends with a second round on new data, no hints.
  • TestsYour score on the interview practice tests is free. Pro adds the full report: what went wrong on each task, and the lesson for it.
  • CertificatePass a timed test with 8 of 10 tasks right and get a certificate with its own page to verify it, and an Add to LinkedIn button. How it works
  • 14 daysNot what you hoped for? Full refund within 14 days, no questions. Refunds
Continue

Sign in or create an account

No password. Your solved challenges then follow you to any device.

Signing in sets one cookie that keeps you signed in. Privacy