When using Autofilter on Excel, how do I ignore 1st 1 or 2 rows

[Guest]

I want to use Auto filter to display certain categories of data, but ingonre
the first row beneath the Headings. Also can I display totals for each view
of Auto filter I choose? ie where the list of deals displayed varies the
total profit etc will also vary. At the moment all I get is the full totals
etc

Apolagies if this is a bit basic but have recently started own business and
am finding difficulty innot havinf instant access to greater jnowledge then
my own!

 

[Pete]

Although it might seem more natural to put totals at the bottom of a
list, I find it better to put them ABOVE the headings - that way, you
can always see them when filters are selected. To do this, highlight
the row containing headings and click Insert | Rows. You can then
highlight the row with the totals in and cut/paste it to the row you
have just inserted. To have the totals show only the total of the
displayed items you should use the formula =SUBTOTALS(9,your_range)
instead of =SUM(your_range) - instead of retypeing these, you can
highlight your total row, then CTRL-H (Find & Replace) andy type SUM(
in the box to find and SUBTOTAL(9, in the box to replace.

Hope this helps,

Pete

 

[Pete]

To follow up, John emailed me directly asking how to treat
=COUNTA(D329) and =AVERAGE(H3:H29) in a similar way.

There are a number of functions that can be used with SUBTOTAL(x,range)
- some of the common values of x are:

1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN

9 - SUM

You can use Help - SUBTOTAL_worksheet_function to find the other
values.

Pete