FILTER

The FILTER function assesses an array based on specified criteria.

FILTER(array, test-array, if-empty)

array: The array to filter.

test-array: A Boolean expression containing a column or row in the array.

if-empty: The value to return if all values in the included array are empty.

Notes

  • If FILTER finds no values that satisfy the specified criteria, an error is returned.

  • You can use the spilled range operator “#” to reference an array created by a formula.

Examples

Given the following data in cells A1:C11:

Sales Rep

Genre

Units

Amanda

Fiction

21

Brandon

Fiction

23

Sofia

Nonfiction

49

Julian

Comic

22

Gregory

Nonfiction

32

Julian

Comic

55

Julian

Comic

44

Sofia

Fiction

31

Amanda

Comic

20

Gregory

Nonfiction

55

=FILTER(A1:C11, A1:A11=A5) returns all of Julian’s sales.

=FILTER(A1:C11, B1:B11=B5) returns all comic book sales.

=FILTER(A1:C11, C1:C11>25) returns all sales transactions greater than 25 units.

=FILTER(A2:C11, (C2:C11>25)*(A2:A11=A5)) returns all of Julian’s sales transactions that exceed 25 units.

See alsoSORTUNIQUE
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
Uporabno?
Omejitev znakov: 250
Zgornja omejitev znakov je 250.
Hvala za povratne informacije.
Morty Proxy This is a proxified and sanitized view of the page, visit original site.