Re: Question: How to create a 'Report' form an Excel database?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 02/29/04 

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.

Relevant Pages

  • Re: The rush hour-and-a-half.
    ... Do you travel for roughly the same amount of time each day? ... It doesn't matter how we travel - whether it's on foot, by car or plane, ... oversaw the opening of the vast new shopping complex in London, ...
    (uk.transport)
  • Re: Great SWT Program
    ... where any key took a different amount of a) force or b) vertical ... travel to activate, except where the key in question was physically ... Sentence fragment of unclear meaning. ...
    (comp.lang.java.programmer)
  • Re: The rush hour-and-a-half.
    ... Do you travel for roughly the same amount of time each day? ... It doesn't matter how we travel - whether it's on foot, by car or ... when/If green taxes were introduced other taxes would be reduced so the net ...
    (uk.transport)
  • Re: Harrahs Casinos in Vegas
    ... that stay I won $600 in a poker tournament but lost $1,200 playing black ... I finished the trip -$600 gambling and -$900 in travel expenses. ... Any amount won over $600 is issued a W2. ...
    (rec.gambling.poker)
  • Question: How to create a Report form an Excel database?
    ... business expense, travel, publications, dues, etc. ... How can I filter and then sum only the contents that have been filtered? ...
    (microsoft.public.mac.office.excel)