**Filter values in an array formula**

There is sometimes a need to filter out a certain amount of data from huge data. Excel makes of the MATCH, IF and ISNUMBER built-in functions to filter out the data contained in an array.

**Generic formula**

`{=COUNT(IF(ISNUMBER(MATCH(data, filter, 0)),data))}`

**How does the formula work?**

- The innermost function used is the MATCH function whose generic parameter ‘data’ are all the values of a named range of cells and these are checked against the values contained in the generic parameter ‘filter’.
- The ISNUMBER function encloses the MATCH function where this function evaluates the matched values for a logical TRUE or FALSE values.
- The IF function encloses the two functions as specified above which is used to exclude all the filtered values.
- The COUNT function only verifies the result.

**Example**

- Let us consider an array set containing different numeric values as shown below.

* Figure 1. The list of random array values and filter value entered *

Let us use the generic formula as shown to exclude some of the values.

*Figure 2. The generic formula used to exclude the filter values.*

The output obtained is as shown below when used the formula.

*Figure 3. The result obtained when used the generic formula*

## Leave a Comment