Advance Filtering question?

[tlapointe1970]

I am attempting to use the advanced filtering options in Excel 2010. I am not having any luck despite following the instructions I found online. Please see attached spreadsheet. I am trying to filter out any row that contain all Zeros.

I must be setting the filter range incorrect or something because nothing happens when I apply the filter. Could someone review and let me know what I am doing wrong?

These are the steps I followed:

when I click on advance filter, I receive a warning about my header row, which I tell it to use as labels and not as numbers.
my list range is set to $C$95:$N$269
my criteria range is set to $C$7:$N$270
'filter in place'

Thank you for your help. Terri

[NBVC]

I think you need to set up the criteria for each header in new rows... when you put them beside each other in same rows, it means OR rather than AND.

See attached and test. (note: you have to select the titles in your database for the source, and the titles in the criteria too....

[NBVC]

Note: You can also use <>0 so that you need only one condition per header, reducing number of total rows used for conditions.

[tlapointe1970]

It still isn't working. I used NBVC suggestion of using <>0, I added additional rows and put the condition on a separate line for each column, I also included the column labels in both the list range and criteria range - I still don't get any filtering. See attached. Thanks

[NBVC]

It worked for me.

Select from C15 to N73, go to Data|Advanced filter... for Criteria, select from C1:N13

Click Ok... it should work.

I attached a copy after filtering.

[OnErrorGoto0]

Clear your whole criteria range, including headers.
Enter this in C2:
=SUM(C16:N16)>0

Now advanced filter your data using C1:C2 as the criteria range.

[NBVC]

How about if the total sum is negative? Should it be included or not?

Maybe that should be:

=SUM(C16:N16)<>0

also, if by chance you want to filter only if some of the column entries are 0, then you need to revert to the separate column idea...

[OnErrorGoto0]

If negatives are an option, then we should probably use COUNTIF in case the negatives cancel out the positives:
=COUNTIF(C16:N16,0)<>12

[NBVC]

Good point

[tlapointe1970]

Excellent - perfect solution to an issue I have been having for months! Thanks. It worked exactly as I want.

[NBVC]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[OnErrorGoto0]

Glad to help. Please don't forget to mark the thread Solved - see FAQ link at the top of the page.