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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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: OT: Travelling to work
    ... got me wondering if that's down to the amount of time it takes to travel ... But given that the colonies ... I'm best excluded from your survey sample. ...
    (uk.games.video.misc)
  • 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)
  • 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)