Filter FUNCTION

For some time now, Microsoft “evangelists” have been talking about about new Excel functions that make it possible to work with dynamic arrays. What is it about? Excel is enriched with a group of functions that provide dynamic content filtering. Since they finally became an integral part of Office 365 Excel in February this year, the following few “recipes” will, through simple examples, show you the new features they bring.

The first function I’ll be writing about is the FILTER function, and its syntax is:

FILTER (<range>;<filter>;[<value if no result found>])

This function has two mandatory and one optional arguments. It is mandatory to specify the range we want to filter and the filter value. Optionally, we can specify the text that will appear if no result has been found.

For example, imagine that we have a simple table with three columns: category, city, and sales. The rows contain sales values ​​according to the set criteria. If we want to highlight the value of sales only for the city of Belgrade we will write the formula:

=FILTER(A2:C13;B2:B13=”Beograd”;”No result found”)

The formula will return all values ​​of the given range where column B has value “Belgrade”. If we would have entered the word “Rome” we would get the message “No result found”.

The filter function may also have multiple criteria. In this case, we enter them in parentheses and put a multiplication sign between them. For example, if we want to see sales in Belgrade, for the value of sales in excess of 60000 we need to enter the formula:

=FILTER(A2:C13;(B2:B13=”Beograd”)*(C2:C13)>60000)

As a result you will get a table filtered upon given criteria. The multiplication sign indicates “logical AND” and then the FILTER function returns values that respect both of the above criteria. We could also specify the sum sign, which means “logical OR”, when the function returns values if at least one of the criteria is met.