Re: Question: How to create a 'Report' form an Excel database?
From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 02/29/04
- Next message: JE McGimpsey: "Re: Animating an excel chart"
- Previous message: JE McGimpsey: "Re: help - how to use a control list to expand data"
- In reply to: Edward M. Baum: "Question: How to create a 'Report' form an Excel database?"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 28 Feb 2004 23:11:12 -0700
In article <BC665E94.9722%embarc@flash.net>,
"Edward M. Baum" <embarc@flash.net> wrote:
> Iąm a comparative novice at Excel, and havenąt been able to find an answer
> to this question in the Help files:
>
> I have organized my tax-deductible expenses as a list in Excel . . . Columns
> for date, amount, and łType˛, the latter being separate categories for
> business expense, travel, publications, dues, etc.
>
> I want to filter the whole list by each łType˛ of expense and sum all those
> within each type . . . Getting the annual total for business expense,
> travel, etc.
>
> When I use the Autofilter, the łsum˛ formula adds in the invisible items in
> the database range as well as the visible ones I want to isolate.
>
> How can I filter and then sum only the contents that have been filtered?
> There must be a way, but I canąt find it.
>
One quick way: the SUM displayed in the status bar when you select a
range of cells totals only the visible cells.
One formula way is to use the SUBTOTAL() function, which only totals
visible cells.
Assume your data is in A1:C100, with row 1 being a header row
Then your subtotal formula would be
=SUBTOTAL(B2:B100,9)
Another way is instead of using Autofilter, to sort your data on column
C (Type) and choose Data/Subtotals, inserting a subtotal for Amount at
every change in Type.
- Next message: JE McGimpsey: "Re: Animating an excel chart"
- Previous message: JE McGimpsey: "Re: help - how to use a control list to expand data"
- In reply to: Edward M. Baum: "Question: How to create a 'Report' form an Excel database?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|