Subtotals to not include Zero's or Null Values

[jlg5454]

Is there a way to have a subtotal formula not include zero or null values? I'm using this basic subtotal formula that is connected to a auto-filter list.

SUBTOTAL(1,C5:C366)

Thanks for any help.

Joe

 

[Mizzness]

Joe,

Try using the custom filter. Select from the drop-down the criteria you need.

 

[Gavona]

Subtotal does not take blanks or non-numeric characters into account. Quite logically it does take zeros into account. So maybe you could Edit,Replace your zeros with blanks or (say) a dash?
If you do not want to do this with your source column you could use a helper column with a formula such as
=(if(AND(C5<>"",C5<>0),C5,"")
(As written this should also take any text entries in column C into your helper column though this may not be a requirement.)

Gavin

 

[SkipVought]

Hi,

Of course, SUBTOTAL [Function Number] 1, is AVERAGE.

Average does not include empty cells. ZEROS would be counted.

So if you want to not include ZEROS, use SUMIF and COUNTIF to calcualte an average, or use the AutoFilter, as has been suggested.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Gavona]

Hey Skip, did you overlook that this is being used on a filtered list? Those functions will include the values from hidden rows.

Regards,


Gavin

 

[jlg5454]

Thanks,

The custom filter does not work right for what I need. This is for a user who's just strictly using the autofilters. I was wondering if there was a formula sort of an If statement to not allow visible zeros within the subtotal.

Thanks,
Joe

 

[Gavona]

Can you not just delete the zeros, or change them to text?
If not make a copy of the column, delete the zeros from that and use that column for your subtotals.



Gavin

 

[rss01]

Could you change your formula to not include zeros

=IF(SUM(B3-A3)=0,"",SUM(B3-A3))

Randy

 

[yogia]

Hi Joe:

You have gotten a good deal of respone. Let me also add the following illustration for you ...

I hope this helps.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com