Sum of Filtered cells

Posted by Uttam on January 04, 2002 12:47 PM

Hello,

I have a spreadsheet which has data in columns D through Z. In Columns A through C, I have values which form the basis for Data Filtering.

The top row in each column has the column Header.

The Second Row has totals of the column underneath.

Data starts in the Third Row.

I would like the Totals generated in the second row to be of those cells which are displayed.

An simplified example might help:

A3 = Nokia
A4 = Central
A5 = Nokia
A6 = Central

B3 = 1000
B4 = 2000
B5 = 3000
B6 = 4000

If no filter is applied, then B2 (which has the total of Column B) should show 10000. If a filter is applied on Column A to show only Nokia, then the total in Column B should show 4000 (1000+3000). And if the filter is for "Central" then the total should show 6000 (2000+4000).

Anyway I can do this?

Thanks in advance.

Posted by Scott on January 04, 2002 12:58 PM

You can use a subtotal formula for this. Here is an example:

=SUBTOTAL(9,D3:D100)

The "9," will sum what is showing. When you use autofilter, it will only sum what is showing on the screen.

Posted by IML on January 04, 2002 1:00 PM

I don't know if this will work with where you are placing it, but if you use this above or below your filtered area, use subtotal. Format would be
=SUBTOTAL(9,B2:B5)

good luck

Posted by Uttam on January 07, 2002 10:49 AM

Thanks a lot - works perfectly!

Regards,

Uttam
========