Thank you for the great Filter examples Mark. I feel like Filter is such a power function, and there are so many ways to use it. Thank you so much for showing the And(*)/Or(+) logic in example 4 and the dependent drop down in example 5, that was neat.
FILTER is probably one of the most powerful functions that we now have. Hopefully Microsoft will release some more dynamic array functions at some point, and that might make FILTER even better.
Sorry to be so offtopic but does anybody know a way to get back into an instagram account?? I stupidly forgot my login password. I would appreciate any assistance you can give me
@Julio Justice Thanks so much for your reply. I got to the site on google and im in the hacking process now. Looks like it's gonna take quite some time so I will get back to you later with my results.
Thank you for the great Filter examples Mark. I have question .. Example 5# How clear the cell in drop down 2 after select a new country in Drop down 1 !! for example when select a new country want clear the city cell because the old city will appear with the new county !! thanks in advanced Mark
Great video! Is it possible to use the FILTER function on a large table and combine it with COUNTIF to get a count of the number of times something shows up in a row in numeric form?
Hello Marc, thank you for this video. it puts everything in good order !!! can you please explain whether we can FILTER an array which is part of a Pivot Table ? i tried it using the "include" parameter with (*) AND, and it returns a #VALUE. (the same thing happens if i use SUMIFS as a workaround) any idea ? Thanks anyway, Be well, and i wish you the best for your new CONSULTANCY business
Hi Megamundus - sorry for the delayed reply. You can use a dynamic array function over a PivotTable, but it is not dynamic. So if the PivotTable expands the formula does not expand to include the new rows. So probably doesn't provide the functionality that you're after.
Thank you for this helpful video. I have a question: I have a 13x3 dynamic array in C3:E15 containing random integers between 1 and 17. If in some other cell I type "=C3#>7", I get, as expected, a 13x3 array of Boolean values. However, if I try to calculate "=FILTER(C3#, C3#>7)", it returns a #VALUE! error. Why? This doesn't make sense, does it?
There is currently no way to format the resulting array as a table. You could use a macro or copy and paste values (but that's probably not what you're looking for).
@@ExcelOffTheGrid Yep, copy-pasting kind of defeats the purpose of using the dynamic array, one may as well filter the table, then copy and paste. Hopefully we’ll get the option somewhere down the line. Thanks a lot!
@@banachdj Each feature is great in its own right, and can be used for amazing things together. However, getting a dynamic array into a Table isn't one of them. I know the feature has been requested through the old Excel User Voice forum, but I've not heard anything since.
In that scenario, it might be easier to use SORT instead of SORTBY. But if you do use SORTBY the challenge is keeping the arrays in sync. Take a look at my SORT and SORTBY videos for more examples.
@@joeblow9284 If using SORT and FILTER it doesn’t matter which nests inside the other, both will work fine. One way probably calculates faster than the other, but unless you’re dealing with huge amounts of data, you’ll never notice.
To FILTER with wildcards, you will need to use some TRUE/FALSE logic. Look at the solution in this forum: www.mrexcel.com/board/threads/filter-function-with-wildcards.1103803/
