Microsoft Advanced Filter works only intermittently. Same exact filter in another workbook with same exact layout doesn't work.

[c_bro2498]

I have a spreadsheet with data for M01.2024 - M12.20324 (12 columns - these are also the headings). The data is either a number or space (I have formatted the data rows as numeric, 0 decimals). I have copied the above headings to another sheet in the same workbook, and underneath put the criteria. There are 12 rows each containing <>0 in the column below on of the above months columns (so in each filter row there is one <>0 and all the rest of the cells have space. Funny thing is that it took me ages to get this to work, and I still don't know why it wasn't working, or how it started working! Anyway I have an identical spreadsheet which just has different data values and this one refuses to work. As I understand it the above arrangement means "filter on all 12 period values <>0 " (ANDs). The other thing to say is that the cells with no value are displayed as space, though they seem to be considered as 0 since they are formatted as numeric - I checked this by trying to replace the " " by o, and it didn't find any " ")

Have I misunderstood the functionality and why does it work only sporadically?

Thanks,

 

[RoryA]

As I understand it the above arrangement means "filter on all 12 period values <>0 " (ANDs)

No - criteria on the same row are AND. Separate rows are OR criteria.

 

[c_bro2498]

Yes - sorry, I meant it only selects rows where at least one the values are <>0 - so OR