The FILTER function in Excel is a powerful tool that allows you to filter a range of data based on specified criteria. Below are 10 examples of how to use the FILTER function, including both data and formulas.
Example 1: Basic Filtering
Data:
| A | B |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
Formula:
=FILTER(A1:B3, B1:B3 > 15)
Result:
| A | B |
|---|---|
| 2 | 20 |
| 3 | 30 |
Example 2: Filtering with Multiple Criteria
Data:
| A | B |
|---|---|
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
Formula:
=FILTER(A1:B3, (B1:B3 > 15) * (A1:A3 < 3))
Result:
| A | B |
|---|---|
| 2 | 20 |
Example 3: Filtering with Text Criteria
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
=FILTER(A1:B3, A1:A3 = "Banana")
Result:
| A | B |
|---|---|
| Banana | 20 |
Example 4: Filtering with OR Logic
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
=FILTER(A1:B3, (A1:A3 = "Apple") + (A1:A3 = "Cherry"))
Result:
| A | B |
|---|---|
| Apple | 10 |
| Cherry | 30 |
Example 5: Filtering with Dates
Data:
| A | B |
|---|---|
| 2023-10-01 | 10 |
| 2023-10-02 | 20 |
| 2023-10-03 | 30 |
Formula:
=FILTER(A1:B3, A1:A3 > DATE(2023, 10, 1))
Result:
| A | B |
|---|---|
| 2023-10-02 | 20 |
| 2023-10-03 | 30 |
Example 6: Filtering with Wildcards
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Apricot | 30 |
Formula:
=FILTER(A1:B3, ISNUMBER(SEARCH("Ap*", A1:A3)))Result:
| A | B |
|---|---|
| Apple | 10 |
| Apricot | 30 |
Example 7: Filtering with Blank Cells
Data:
| A | B |
|---|---|
| Apple | 10 |
| 20 | |
| Cherry | 30 |
Formula:
=FILTER(A1:B3, A1:A3 <> "")
Result:
| A | B |
|---|---|
| Apple | 10 |
| Cherry | 30 |
Example 8: Filtering with Multiple Columns
Data:
| A | B | C |
|---|---|---|
| Apple | 10 | 100 |
| Banana | 20 | 200 |
| Cherry | 30 | 300 |
Formula:
=FILTER(A1:C3, (B1:B3 > 15) * (C1:C3 < 250))
Result:
| A | B | C |
|---|---|---|
| Banana | 20 | 200 |
Example 9: Filtering with Dynamic Arrays
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
=FILTER(A1:B3, B1:B3 > 15)
Result:
| A | B |
|---|---|
| Banana | 20 |
| Cherry | 30 |
Example 10: Filtering with Error Handling
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | #N/A |
| Cherry | 30 |
Formula:
=FILTER(A1:B3, NOT(ISERROR(B1:B3)))
Result:
| A | B |
|---|---|
| Apple | 10 |
| Cherry | 30 |
These examples demonstrate the versatility of the FILTER function in Excel, allowing you to manipulate and extract data based on various conditions and criteria.
No comments:
Post a Comment