filtering more than 1 column

  • Thread starter Thread starter MS
  • Start date Start date
M

MS

I have a spreadsheet with 2 columns like this:

Col A Col B
1 1 2
2 0 0
3 2 3
4 0 4
5 0 0
6 5 0
7 6 2

Is there a way to filter out Col A and B when both items
are 0? The information in Col A and B should be displayed
if either contains a number. The only rows I do not want
to see are (2) and (5) since both have 0. Thanks for the
help....
 
Hi
one way: use 'Data - Filter - Advanced Filter'

another way: add a helper column C with the formula in C1
=IF(AND(A1=0,B1=0),1,0)
copy down and filter with this column
 
You can add and extra column to your table, and use an AutoFilter, or
add a criteria area to the worksheet, and use an Advanced Filter.

To use either type of filter, add a heading row to your table.
Insert a blank row above the first row of numbers
In cell A1 enter "Amount1" and in cell B1, enter "Amount2"
In cell C1, enter "Filter"
In cell C2, enter: =AND(A2=0,B2=0)
Copy the formula down to the last row of data (to do this,
point to the fill handle at the bottom right of cell C2.
When the pointer changes to a black fill handle, double-click)
Select a cell in the table, and choose Data>Filter>AutoFilter
From the dropdown list in cell C1, choose FALSE
 
Back
Top