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
- Aug 07 Thu 2008 20:46
When using Autofilter on Excel, how do I ignore 1st 1 or 2 rows
close
全站熱搜
留言列表
發表留言