Multiplication creates AND logic (i.e., all the criteria must be TRUE). The formula in cell F3 is: =FILTER(B3:D10,(C3:C10>50)*(D3:D10>30))įor anybody who has used the SUMPRODUCT function, this method of applying multiple conditions will be familiar. Amazing stuff! Example 4 – Using FILTER with multiple criteria.Įxample 4 shows how to apply FILTER with multiple criteria. New records added to the table which meet the criteria are automatically added to the spill range of the function. This example shows how the FILTER function responds when linked to an Excel table. This formula would result in the following:Įxample 3 – FILTER expands automatically when linked to a table The following would show n/a in the Invoice Value and Days Due columns. If we wanted to display a result in each column, we could include a constant array within the if_empty argument. In the screenshot above, “ No Results” displays instead of the #CALC! error. Thankfully, Microsoft has given us the if_empty argument, which displays a message if there are no rows returned. The formula in cell F3 is: =FILTER(B3:D10,C3:C10>200)Īs no rows meet the criteria of Invoice Value being higher than 200), the FILTER cannot return a value, so the #CALC! error is displayed. The screenshot below displays what happens when the result of the FILTER function has zero results – we get the new #CALC! error. Example 2 – #CALC! error caused by the FILTER function This single formula is returning 2 rows and 3 columns of data where the values in C3-C10 are higher than 100. In this example, cell F3 contains a single formula, but this formula returns an array of values into the neighboring rows and columns. Example 1 – FILTER returns an array of rows and columns The following examples illustrate how to use the FILTER function.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |