The SUMPRODUCT function is one of those hero functions that, once discovered, you wonder how you ever managed without. It does not just do “what it says on the tin”, there’s a lot more to it than that. In particular, it’s one of the most powerful and flexible filter functions in Excel. And so much better than SUMIF or SUMIFS.
Excel FILTER Function
Excel FILTER Function
I really like Excel’s AutoFilter but I think the Excel FILTER function does the job better because it’s a live formula. And it’s an extraction, you don’t have to filter your data in place. This means it’s easy to have your results wherever you want them without having to bother with the Advanced Filter.
It’s a fairly new function so, as usual, the first thing is to check out whether you have it or not. Just click on an Excel cell, type-in “=FI” and see if you can spot FILTER in the pop-up list.
Here’s the data I want to filter, it’s a simple Excel list with 6 columns, A to F, with headings entered into the top row.
Basic Excel Filter Formula
And here’s the basic filter formula. I want to filter the list to just show the rows where I have “Agency” in column C.
Sorting the Results
You can sort the results, if you want to, by wrapping the SORT function around the FILTER function, like this:
And that sorts the results in alpha order on the first column. But you can change this by entering extra information into the sort. In this example, I’m sorting the results by the 6th column (that’s my last column) with the highest number listed first. So, that’s minus 1 for descending order. Here’s the formula:
Filtering the Filter
Here’s the original filter formula again. You’ll see that the formula gives you all the columns from your data. You don’t always want that. To control the results you need to filter your filter.
To do this put your original filter inside another filter and use an array of values to declare which columns to display in the results. The array is a set of zeros or ones inside a pair of braces (squiggly brackets). A one means show the column, a zero hides the column. Here’s my formula; out of the 6 columns, I’m showing only the second, third and sixth columns:
More Complex Excel Filter Formulas
You can create complex filters by multiplying different expressions in your filter. Don’t worry about the internal logic, just copy and paste an example and adapt it to your own needs. I always do!
In this example, include everything where you have “Agency” in column C and the value in column E is 1000 or more:
In this one, include everything where the value in column E is between 250 and 1000:
In this one, include everything where the value in column E is between 250 and 1000, but not where the value in column C is “Agency”:
Multiplication gives you a logical AND condition. If you want a logical OR, then use the plus sign. For example, this formula means include everything where the value in column C is either “Agency” or “In Store”:
That’s all for now, best of luck with your filters!
The FILTER function is just one of the new type of Excel functions, Dynamic Array Functions that were released with Excel 365. To learn more about these revolutionary functions, come along on our dedicated Excel Dynamic Array Functions Course.
Call 020 7920 9500 now for details.
It’s easy to calculate someone’s age from their date of birth if you know about Excel’s DATEDIF function, unfortunately it’s easy to miss this function as it is not documented. Excel will not help you fill in the DATEDIF function interval values, you need to see the list here.
Our Excel double click tricks are some of those little things that make your life so much easier. You probably know most of them already. Or do you? I think that anyone who uses Excel regularly should know them.
Complicated text formulas using either ampersands and the CONCATENATE function are the bane of our life. Not any more! Excel new text functions will really help us nail those text formulas. We’ll be looking at the CONCAT function and the TEXTJOIN function.
Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.
You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…