FILTER function

Question

Hi all

I have created a basic FILTER function, as shown. It works fine, however, I've just realised that when the data doesn't meet the criteria I have specified, it simply returns nothing - not the text "No entries"? Can anybody see what I've done wrong? TIA, Louise

Answer 1

What does the notation A.:.G mean? It causes an error in my version of Excel (Microsoft 365 version 2411)

Answer 2

I may be wrong, but if there is 1 row returned, wouldn't it be the header row?

If there are 2 or more, then there must be valid data??

I would not do it like this, but just an idea below.

( For the poster, the use of G:G is rather outdated. )

Answer 3

Thank you. You have included the header row in the range to be filtered. Since a text value is larger than any number value, the formula will always return the header row, even if none of the data rows match the criteria:

If you use the (not realistic) condition <0, the header row doesn't match the criteria either, so the formula returns "No returns":

As a workaround, you might check whether any data rows meet the criteria:

The formula is

=IF(COUNTIF('Dynamic Filters Data'!G:G, ">12000")=0, "No returns", FILTER('Dynamic Filters Data'!A:G,'Dynamic Filters Data'!G:G>12000,"No returns"))

Answer 4

Hi

Strange isn't it. I have produced other reports from the same data set and it works fine on those. Very odd... I've clearly done something wrong.

Here is a link to the data. Don't worry, it's all completely fictitious. Excel Filter file. Thank you, Louise

Answer 5

That is strange - the formula looks OK.

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.