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

[Edward M. Baum]

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.

Thanks in advance.

Ed in Dallas

 

[JE McGimpsey]

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.