# Excel FILTER Function With Multiple Criteria, Sorted With SORT and SORTBY

> Filter Excel data on several conditions with FILTER, keep only the columns you need, and sort the result with SORT and SORTBY, with fixes for #CALC!, #SPILL! and #VALUE!.

Source: https://www.epifive.com/blog/excel-filter-function-multiple-criteria-sort · Published 2026-11-01 · EpiFive

## Key takeaways

- In FILTER, multiplying conditions with * works as AND and adding them with + works as OR, with each condition wrapped in its own brackets.
- The third FILTER argument, if_empty, prevents the #CALC! error that appears when no rows match.
- SORT sorts a result by one column number, while SORTBY sorts by several ranges, each with its own 1 (ascending) or -1 (descending) order.
- CHOOSECOLS and TAKE are listed by Microsoft for Excel for Microsoft 365 and Excel 2024, while FILTER, SORT and SORTBY are also listed for Excel 2021.
- A #SPILL! error usually means something is in the way of the results; clear the blocking cells or move the formula out of the Excel table.

To filter Excel data on more than one condition, wrap each condition in brackets inside FILTER and join them with `*` for AND or `+` for OR, for example `=FILTER(A2:F13,(C2:C13="East")*(F2:F13>=1500),"No matches")`. Wrap that formula in SORT to order the result by one column, or in SORTBY to sort by several columns, and the whole report updates by itself when the data changes.

This guide builds one small sales report step by step, and every formula comes with the exact result it returns on the sample data, so you can check your own sheet as you go.

## Who this is for and what you will have at the end

This is for anyone who keeps a list in Excel, such as orders, invoices or tickets, and keeps reapplying AutoFilter every time someone asks for "East region, over 1,500, biggest first". FILTER does the same job with a formula, so the answer stays live.

By the end you will have FILTER formulas for AND, OR, "contains" text and date ranges, a result with only the columns you choose, sorting by one or more columns, a top 3 list, a report driven by input cells, and fixes for the errors these formulas produce.

## Before you start: Excel versions and the sample data

These are dynamic array functions. A dynamic array formula is typed in one cell and its results "spill" into the cells next to it. Check that your Excel version has the functions you plan to use. The table below follows the Applies to lists on Microsoft's function pages.

| Function | Listed by Microsoft for |
| --- | --- |
| FILTER, SORT, SORTBY, LET | Excel for Microsoft 365 (Windows and Mac), Excel 2024, Excel 2021, Excel for iPad, iPhone and Android |
| CHOOSECOLS, TAKE | Excel for Microsoft 365 (Windows and Mac), Excel 2024 |

If you share the workbook with people on an older version, see the #NAME? fix in the errors section.

### The sample sales table

Type or paste this table into a new sheet so that the header Order is in cell A1 and the last row ends in F13. The data is fictional Contoso-style sales.

| Order | Date | Region | Product | Units | Revenue |
| --- | --- | --- | --- | --- | --- |
| 1001 | 2026-01-05 | East | Laptop Pro 14 | 3 | 3600 |
| 1002 | 2026-01-09 | West | Wireless Mouse | 25 | 625 |
| 1003 | 2026-01-14 | East | USB-C Dock | 8 | 1440 |
| 1004 | 2026-01-22 | North | Laptop Air 13 | 5 | 4500 |
| 1005 | 2026-02-03 | West | Laptop Pro 14 | 4 | 4800 |
| 1006 | 2026-02-11 | South | Monitor 27 | 6 | 1920 |
| 1007 | 2026-02-17 | East | Wireless Mouse | 40 | 1000 |
| 1008 | 2026-02-25 | North | USB-C Dock | 10 | 1800 |
| 1009 | 2026-03-04 | East | Laptop Air 13 | 2 | 1800 |
| 1010 | 2026-03-12 | South | Laptop Pro 14 | 6 | 7200 |
| 1011 | 2026-03-19 | West | Monitor 27 | 3 | 960 |
| 1012 | 2026-03-27 | East | Monitor 27 | 9 | 2880 |

So the data rows are A2:F13: column A is Order, B is Date, C is Region, D is Product, E is Units and F is Revenue. Make sure the dates are real Excel dates, not text.

Unless a section says otherwise, type each formula into cell H4 and replace it as you work through the examples. H4 has empty space to the right and below, which the results need.

## FILTER syntax and a first formula

Microsoft documents the syntax as:

```text
=FILTER(array,include,[if_empty])
```

- **array** is the range to filter, here A2:F13.
- **include** is a Boolean (TRUE/FALSE) array whose height or width matches the array. In practice it is a test on one column, such as C2:C13="East".
- **if_empty** is optional: the value to return when nothing matches.

To list every East order:

```text
=FILTER(A2:F13,C2:C13="East","No matches")
```

Result: five rows spill into H4:M8, orders 1001, 1003, 1007, 1009 and 1012, with all six columns. Only H4 contains the formula. If you select another cell in the result, the formula appears ghosted (grayed out) in the formula bar, and Microsoft notes that only the first cell of the spill area can be edited.

If the Date column of the result shows five-digit numbers such as 46027, those are Excel date serial numbers. Select that column of the result and apply Short Date from the Number group on the Home tab.

### Why if_empty matters

Try a region that does not exist:

```text
=FILTER(A2:F13,C2:C13="Central","No matches")
```

Result: a single cell that says No matches. Remove the third argument and the same formula returns `#CALC!`, because, in Microsoft's words, Excel does not currently support empty arrays. Use `""` as if_empty if you would rather show a blank cell.

## FILTER with multiple criteria

Each condition produces a column of TRUE and FALSE values, one per row. Multiplying or adding two of those columns gives a number for each row, with TRUE counting as 1 and FALSE as 0, and FILTER keeps the rows where that number is not 0.

### AND: multiply the conditions with *

A row is kept only when every condition is TRUE, because 1 times 1 is 1 and anything times 0 is 0. East orders with revenue of at least 1,500:

```text
=FILTER(A2:F13,(C2:C13="East")*(F2:F13>=1500),"No matches")
```

Result: three rows, orders 1001 (3600), 1009 (1800) and 1012 (2880). Orders 1003 and 1007 are East but below 1,500, so they drop out.

Put brackets around each condition. Microsoft's operator precedence table ranks multiplication and addition above comparison operators such as = and >=, so without brackets Excel multiplies before it compares and the logic breaks.

### OR: add the conditions with +

A row is kept when at least one condition is TRUE, because the sum is then 1 or more. North or South orders:

```text
=FILTER(A2:F13,(C2:C13="North")+(C2:C13="South"),"No matches")
```

Result: four rows, orders 1004, 1006, 1008 and 1010.

### Combine AND and OR

Put the OR part in its own set of brackets, then multiply by the AND part. North or South orders with revenue over 2,000:

```text
=FILTER(A2:F13,((C2:C13="North")+(C2:C13="South"))*(F2:F13>2000),"No matches")
```

Result: two rows, orders 1004 (4500) and 1010 (7200). Orders 1006 (1920) and 1008 (1800) are in the right regions but not over 2,000.

### Contains text: ISNUMBER and SEARCH

An equals sign only matches the whole cell. To keep rows where the product name contains a word, use SEARCH, which returns the position of the text or `#VALUE!` when it is not found. ISNUMBER turns that into TRUE or FALSE:

```text
=FILTER(A2:F13,ISNUMBER(SEARCH("laptop",D2:D13)),"No matches")
```

Result: five rows, orders 1001, 1004, 1005, 1009 and 1010, every Laptop Pro 14 and Laptop Air 13. SEARCH is not case-sensitive, so "laptop" matches "Laptop". Use FIND in place of SEARCH when case matters.

### Date range

Compare the Date column with two dates built by DATE(year,month,day) and multiply the tests. All February 2026 orders:

```text
=FILTER(A2:F13,(B2:B13>=DATE(2026,2,1))*(B2:B13<=DATE(2026,2,28)),"No matches")
```

Result: four rows, orders 1005, 1006, 1007 and 1008. DATE builds each date from year, month and day numbers, so there is no doubt about which day the formula means.

## Return only the columns you need with CHOOSECOLS

A report often needs just a few columns. CHOOSECOLS returns the columns you list by number:

```text
=CHOOSECOLS(array,col_num1,[col_num2],...)
```

Pick the columns first, then filter. Order, Product and Revenue for East:

```text
=FILTER(CHOOSECOLS(A2:F13,1,4,6),C2:C13="East","No matches")
```

Result: five rows and three columns: 1001 Laptop Pro 14 3600, 1003 USB-C Dock 1440, 1007 Wireless Mouse 1000, 1009 Laptop Air 13 1800, and 1012 Monitor 27 2880.

The order matters. If you write `=CHOOSECOLS(FILTER(...),1,4,6)` and nothing matches, FILTER returns the single cell "No matches", and CHOOSECOLS then returns `#VALUE!` because, as Microsoft's page explains, a column number larger than the number of columns is an error. Filtering last avoids that.

CHOOSECOLS is listed for Microsoft 365 and Excel 2024. In Excel 2021 you can get the same three columns by filtering a second time across the columns with a row of 1s and 0s, where 1 keeps a column:

```text
=FILTER(FILTER(A2:F13,C2:C13="East","No matches"),{1,0,0,1,0,1})
```

Result: the same five rows and three columns as above.

## Sort the results with SORT

SORT orders a range or array by one of its columns:

```text
=SORT(array,[sort_index],[sort_order],[by_col])
```

- **sort_index** is the column number inside the array to sort by. If you leave it out, the first column is used.
- **sort_order** is 1 for ascending (the default) or -1 for descending.
- **by_col** is FALSE (the default) to sort rows, or TRUE to sort columns.

East orders, largest revenue first. Revenue is the sixth column of A2:F13:

```text
=SORT(FILTER(A2:F13,C2:C13="East","No matches"),6,-1)
```

Result: orders 1001 (3600), 1012 (2880), 1009 (1800), 1003 (1440) and 1007 (1000), in that order.

The sort_index counts columns in the array SORT receives, not sheet columns. If you sort the three-column CHOOSECOLS result, Revenue is column 3, not 6.

## Sort by multiple columns with SORTBY

SORTBY sorts one array by other ranges and accepts several sort keys:

```text
=SORTBY(array,by_array1,[sort_order1],[by_array2,sort_order2],...)
```

Each by_array must be a single column (or a single row), and Microsoft states that all arguments must be the same size. A sort order other than 1 or -1 returns `#VALUE!`. Microsoft's SORT page also recommends SORTBY for sorting data in the grid, because it refers to a range rather than a column number and keeps working when columns are added or removed.

The whole table by Region A to Z, then by Revenue largest first within each region:

```text
=SORTBY(A2:F13,C2:C13,1,F2:F13,-1)
```

Result: East 1001, 1012, 1009, 1003, 1007; North 1004, 1008; South 1010, 1006; West 1005, 1011, 1002.

### SORTBY on a filtered result

Every by_array must match the size of the array, so C2:C13 will not work against a filtered result with fewer rows. Filter the sort columns with the same condition instead. Orders with revenue of at least 1,500, by Region, then by Revenue descending:

```text
=SORTBY(FILTER(A2:F13,F2:F13>=1500,"No matches"),FILTER(C2:C13,F2:F13>=1500),1,FILTER(F2:F13,F2:F13>=1500),-1)
```

A shorter version names the filtered result once with LET and picks the sort columns out of it with CHOOSECOLS (Microsoft 365 and Excel 2024):

```text
=LET(data,FILTER(A2:F13,F2:F13>=1500,"No matches"),SORTBY(data,CHOOSECOLS(data,3),1,CHOOSECOLS(data,6),-1))
```

Result for both: eight rows, East 1001 (3600), 1012 (2880), 1009 (1800); North 1004 (4500), 1008 (1800); South 1010 (7200), 1006 (1920); West 1005 (4800).

## Show the top N with TAKE

TAKE returns a number of rows or columns from the start or end of an array:

```text
=TAKE(array,rows,[columns])
```

A negative number takes from the end. Sort descending, then take the first rows. Top 3 orders by revenue:

```text
=TAKE(SORT(A2:F13,6,-1),3)
```

Result: 1010 (7200), 1005 (4800) and 1004 (4500).

Top 3 East orders:

```text
=TAKE(SORT(FILTER(A2:F13,C2:C13="East","No matches"),6,-1),3)
```

Result: 1001 (3600), 1012 (2880) and 1009 (1800). TAKE is listed for Microsoft 365 and Excel 2024.

## Build a live report from criteria cells

A report is easier to reuse when the criteria sit in cells. Set up:

- H1: Region, and I1: East
- H2: Min revenue, and I2: 1500

Then in H4:

```text
=SORT(FILTER(A2:F13,(C2:C13=I1)*(F2:F13>=I2),"No matches"),6,-1)
```

Result: 1001 (3600), 1012 (2880) and 1009 (1800). Type West in I1 and the result changes at once to 1005 (4800) alone.

To let a blank region cell mean "all regions", add an OR test on the input cell itself:

```text
=FILTER(A2:F13,((C2:C13=I1)+(I1=""))*(F2:F13>=I2),"No matches")
```

With I1 cleared and I2 still 1500, the result is the eight orders of 1,500 or more: 1001, 1004, 1005, 1006, 1008, 1009, 1010 and 1012.

Keep the criteria cells outside the area the result will fill. If an input cell sits inside the spill range, the formula shows `#SPILL!` instead of results.

## Refer to the spilled results with #

To use the whole spilled result in another formula, add the spilled range operator `#` after the address of the formula cell. With `=FILTER(A2:F13,C2:C13="East","No matches")` in H4, enter these in empty cells outside H4:M8, such as O1 and O2:

```text
=ROWS(H4#)
=SUM(TAKE(H4#,,-1))
```

Results: 5 matching orders, and 10720 total East revenue (TAKE with -1 columns returns the last column, Revenue). When the filter returns more or fewer rows, both formulas follow automatically. If FILTER shows "No matches", ROWS returns 1, so test for that text before you report a count.

Microsoft notes that the spilled range operator does not support references to closed workbooks; those return `#REF!` until you open the source workbook.

## Use an Excel table so new rows are included

A fixed range like A2:F13 does not grow when you add row 14. Convert the data to an Excel table: select a cell in the data, choose Home > Format as Table, pick a style, and confirm the header row. Then select a cell in the table and type Sales in the Table Name box on the Table Design tab.

The table name on its own refers to the data rows without the header, and Sales[Region] refers to one column:

```text
=FILTER(Sales,(Sales[Region]="East")*(Sales[Revenue]>=1500),"No matches")
```

Result: orders 1001, 1009 and 1012, as before. Add an East order with revenue of 2,400 directly under the table and it becomes part of the table, so it appears in the result without editing the formula.

FILTER does not return the header row. To label the output, put this in the cell above the formula, for example in H3:

```text
=Sales[#Headers]
```

Place these formulas on the grid outside the table. Microsoft states that spilled array formulas are not supported inside Excel tables.

## Common errors and fixes

| Error | Cause | Fix |
| --- | --- | --- |
| #CALC! | No rows match and if_empty is missing | Add a third argument such as "No matches" or "" |
| #SPILL! | Cells in the spill range are not empty | Select the formula, choose Select Obstructing Cells, clear or move that content |
| #SPILL! | Formula is inside an Excel table | Move it outside the table, or convert the table to a range |
| #SPILL! | Spill range contains merged cells | Unmerge them or move the formula |
| #VALUE! | include range is a different size from array | Make both ranges start and end on the same rows |
| #VALUE! | SORTBY sort order is not 1 or -1 | Use 1 for ascending or -1 for descending |
| #VALUE! | CHOOSECOLS column number is 0 or too large | Count columns in the array it receives |
| #NAME? | Text criterion without quotes, or function not available | Add quotes; check your Excel version |
| #REF! | Source or spilled range is in a closed workbook | Open the source workbook |

### #SPILL!

When you select the formula cell, a dashed border shows where the results want to go, and the error alert offers Select Obstructing Cells to jump to the blocker. Microsoft also lists results that would run past the edge of the worksheet and running out of memory; reference a smaller range for both. To keep a formula that sits in a table, convert the table with Table Design > Tools > Convert to range.

### #VALUE! with mismatched ranges

`=FILTER(A2:F13,C2:C12="East","No matches")` tests 11 rows against 12 data rows and returns `#VALUE!` in current Excel. An Excel table avoids this, because Sales and Sales[Region] always cover the same rows.

### #NAME?

Microsoft's #NAME? page gives a misspelled function name as the top reason, and text without double quotation marks as another: `C2:C13=East` returns `#NAME?`, while `C2:C13="East"` works. A function missing from your version, such as CHOOSECOLS in Excel 2021, is not recognized either. For workbooks shared with older versions, Microsoft advises avoiding features those users lack and running the Compatibility Checker to find dynamic array formulas, which older versions show as legacy array formulas that cannot resize.

## Checklist

- Each FILTER has a third argument so an empty result shows text instead of `#CALC!`.
- Every condition is in its own brackets; `*` joins AND conditions and `+` joins OR conditions.
- The include ranges start and end on the same rows as the array.
- SORT uses the column number inside the array it sorts; SORTBY uses ranges of the same size with 1 or -1.
- CHOOSECOLS runs before FILTER, not after, so an empty result does not turn into `#VALUE!`.
- Criteria cells and other content sit outside the spill range.
- The source data is an Excel table, and the formulas sit outside it.
- Everyone who opens the file has a version that lists the functions used.

For more on dynamic array functions, see [remove duplicates and count unique values in Excel](https://www.epifive.com/blog/excel-remove-duplicates-count-unique-values), and for tidying a messy list before you filter it, see [clean messy spreadsheet data with AI in Excel and Google Sheets](https://www.epifive.com/blog/ai-spreadsheet-data-cleaning-excel-copilot-sheets). [More Microsoft 365 tutorials](https://www.epifive.com/microsoft-365-tutorials) cover Excel, Outlook and the rest of the suite.

## References

- [FILTER function](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)
- [SORT function](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)
- [SORTBY function](https://support.microsoft.com/en-us/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f)
- [CHOOSECOLS function](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)
- [TAKE function](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)
- [LET function](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)
- [SEARCH and SEARCHB functions](https://support.microsoft.com/en-us/office/search-and-searchb-functions-9ab04538-0e55-4719-a72e-b6f54513b495)
- [IS functions](https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665)
- [Calculation operators and precedence in Excel](https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-4975-4d31-b2b8-7af9e0e2878a)
- [DATE function](https://support.microsoft.com/en-us/office/date-function-e36c0c8c-4104-49da-ab83-82328b832349)
- [Dynamic array formulas and spilled array behavior](https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531)
- [Spilled range operator](https://support.microsoft.com/en-us/office/spilled-range-operator-3dd5899f-bca2-4b9d-a172-3eae9ac22efd)
- [Dynamic array formulas in non-dynamic aware Excel](https://support.microsoft.com/en-us/office/dynamic-array-formulas-in-non-dynamic-aware-excel-696e164e-306b-4282-ae9d-aa88f5502fa2)
- [How to correct a #SPILL! error](https://support.microsoft.com/en-us/office/how-to-correct-a-spill-error-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023)
- [How to correct a #CALC! error](https://support.microsoft.com/en-us/office/how-to-correct-a-calc-error-d6ee03c5-daf6-426a-8df5-4b284730ab1b)
- [How to correct a #NAME? error](https://support.microsoft.com/en-us/office/how-to-correct-a-name-error-b6d54e31-a743-4d7d-9b61-40002a7b4286)
- [Using structured references with Excel tables](https://support.microsoft.com/en-us/office/using-structured-references-with-excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e)
- [Create and format tables](https://support.microsoft.com/en-us/office/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664)

## Frequently asked questions

### How do I use the FILTER function with multiple criteria in Excel?

Put each condition in brackets and multiply them for AND, for example =FILTER(A2:F13,(C2:C13="East")*(F2:F13>=1500),"No matches"). Add them with + for OR, for example (C2:C13="North")+(C2:C13="South"). Microsoft's FILTER page uses exactly these two patterns.

### How do I sort by multiple columns in Excel with a formula?

Use SORTBY and give it one sort column and sort order per key, for example =SORTBY(A2:F13,C2:C13,1,F2:F13,-1) sorts by region A to Z and then by revenue from largest to smallest. A sort order of 1 is ascending, -1 is descending, and any other value returns #VALUE!.

### Why does my FILTER formula return

FILTER returns #CALC! when no rows match and you left out the third argument, because Excel cannot return an empty array. Add if_empty, for example "No matches" or "", so the formula shows that value instead of an error.

### What does the

The formula returns several values but Excel cannot place them on the grid. The most common cause is a cell in the spill range that is not empty; select the formula cell, use Select Obstructing Cells, and clear or move that content. Spilled formulas also cannot sit inside an Excel table or spill into merged cells.