FILTER() is not working in OneDrive

Occasional Contributor

Hey guys, anyone knows what could be the reason why FILTER formula does not work when I uploaded the file to the OneDrive. It works perfectly when I open the file in local excel but not OneDrive. No error is shown. I have no idea what is the problem in this case. 

 

 

When I open the file with local excel

Screenshot 2022-03-07 at 10.19.18 AM.png

 

When I open the file at OneDrive

Screenshot 2022-03-07 at 10.21.25 AM.png

 

Anyone encounter or know what is the problem?

 

Thank you. 

3 Replies
I know that files that I store in OneDrive, files that use FILTER, work just fine when opened from OneDrive.

I"m presuming (as you are too) that the IF condition isn't the issue--that you generated those two screen captures at basically the same time. Otherwise, it is conceivable that FILTER working just fine, given that the IF condition can yield blank results under certain circumstances.

Have you tried FILTER in a simpler formula (i.e., without nesting it within an IF)?
Hi @mathetes, thank you for your reply. You mean I can try to amend the formula to FILTER(D55:D69,G55:G69>0 that's it?

@Jesling As @mathetes suggests the formula is technically correct, but I would like to add that it isn't very meaningful. The portion "FILTER(D55:D69,G55:G69<0)=0" will ALWAYS return FALSE if at least one match is found, and then the second filter will be applied.  Or it returns an error if no items are found that match the criterion of being <0. Then the IFERROR function kicks in.

You can use the third (optional) [if_empty] argument of the FILTER function to capture instances where no matches are found and can re-write the formula to:

=FILTER(D55:D69,G55:G69<0,"")