Filter group of records with sum not equal to zero

[Ashvin]

Hi,
How can I filter this spreadsheet so that I can only see the entries where the sum is not equal to zero. For example I would like to see rows 2 to 5 and 24 to 26 because the sum is not zero. I do not want see rows 6 to 23 and 27 to 37 because the sum is zero. I can filter by color if I color all my non zero entries but spreadsheet may have more than 5000 lines and it is very time consuming.

 

[AVK]

select filter & select greater than or equal to 1. By color you have option in excel 2007.

 

[Nebu]

You can use conditional formatting all the cells with zero , its easy and fast

 

[jeffreyweir]

I think you guys are missing the point. Asvin needs to filter out all rows that correspond to SUM: 0

So if you look at the sample data, Asvin wants to be able to hide rows 14 to 29, then hide rows 35 to 37, and so on.

 

[jeffreyweir]

Asvin: Put this in G6 and copy it down:

=IF(LEFT(F6,5)="Sum: ",IF(LEFT(F6,6)="Sum: 0","Sum: 0",""),IF(H7="Sum: 0","Sum: 0",""))

Then using the autofilter, filter out anything with "Sum: 0"

See attached.

 

[jeffreyweir]

This is an even simpler formula:
=IF(LEFT(F6,3)="Sum",F6,H5)
Put it in G6 and copy down to the bottom of the data. Then use the autofilter to filter out any "Sum: 0" rows.

 

[Syedali]

Hi,
Try using filter does not equal to "Sum: 0.00 ".

 

[Ashvin]

Hi Jeffrey,
The first formula works perfect. Thank You very much, this will be big time saver. Greatly appreciate your help.
Ashvin

 

[jeffreyweir]

Try using filter does not equal to "Sum: 0.00 ".

That will not work. If you look at the sample data, Asvin wants to be able to hide rows 10 to 28, then hide rows 31 to 35, and so on. Filtering the recordset on <> "Sum: 0.00" will only hide rows 14, 18, 22, 28 etc.

Ashvin needs any row associated with a "Sum: 0.00" to be excluded, not jut the rows with "Sum: 0.00" in them.

My formula allows him to do just that.