Column Sums & Auto Filtering

[BrianKoz]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I have figured out the formula to provide the sum of a list of values in a column that are NOT hidden: =SUBTOTAL(109,W2:W506)

This works great, although a new issue is preventing me from utilizing these function to its fullest.

There are several columns of information - monetary values available, monetary values used, Program A cost, etc.

I applied the first mentioned formula when the sheet was completely unfiltered.

If I only want to show values greater than $X for Program A in Column whatever, this is not a problem.

THE PROBLEM. When I filter based on a criteria, the row where all of the formulas (=SUM, =COUNT, etc) are located becomes hidden. The filtered criteria does not appear in this line.

Is there a way to lock rows to show up no matter the filtered criteria?
Is there a way to add a "footer-like" feature to a sheet?

A PivotTable could work, but locking rows seems like much less work and a rather valuable feature for future use.

Any help would be much appreciated!

thanks,
Brian

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)

I have figured out the formula to provide the sum of a list of values in a
column that are NOT hidden: =SUBTOTAL(109,W2:W506)

This works great, although a new issue is preventing me from utilizing these
function to its fullest.

There are several columns of information - monetary values available,
monetary values used, Program A cost, etc.

I applied the first mentioned formula when the sheet was completely
unfiltered.

If I only want to show values greater than $X for Program A in Column
whatever, this is not a problem.

THE PROBLEM. When I filter based on a criteria, the row where all of the
formulas (=SUM, =COUNT, etc) are located becomes hidden. The filtered
criteria does not appear in this line.

Is there a way to lock rows to show up no matter the filtered criteria?
Is there a way to add a "footer-like" feature to a sheet?

A PivotTable could work, but locking rows seems like much less work and a
rather valuable feature for future use.

Not sure I understand what you're after, but though there's no way to
lock rows, you don't have to include the sum/count rows in the
autofilter.

You could also put the summary formulas at the top of the worksheet (or
even on a separate sheet), where they wouldn't get filtered.