close

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!

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 amp; Replace) andy type SUM(
in the box to find and SUBTOTAL(9, in the box to replace.

Hope this helps,

PeteTo 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 a

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

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 software 的頭像
    software

    software

    software 發表在 痞客邦 留言(0) 人氣()