Count, Sum, and Average at the Bottom of a Filter

Quote from kenshin

Is there a way to make Excel display either the count of rows, sum of numbers, or average of numbers (depends on the column) when you do a filter?

I have a master table where I want to quickly get these numbers when i do filters.

Thanks in advance.

Hi,

try,

=SUBTOTAL(function_number,range_of_cells)

The function number indicates which function you want -- count, sum, average, etc. See the help menu for a list of corresponding numbers.

Quote from kenshin

Hi I tried what you said, and placed the subtotal function at the bottom of the column, last row of the worksheet.

when i double checked the sum of the numbers of that same column, the correct total was 65000. when i checked the subtotal figure, it was 260 only. the auto filter was on but there was no criteria filters applied yet.

what could be wrong with the subtotal formula?

First, let's make sure that you're using the correct formula. If you want to add the amounts for a filtered column, let's say Column B, you should be using...

=SUBTOTAL(9,B1:B100)

If you want to count the number of rows containing numbers...

=SUBTOTAL(2,B1:B100)

Note that if you have an unfiltered table and you place the Subtotal formula at the end of the column you wish to total, you'll get the total for all cells. When you apply the filter, then the Subtotal function will automatically total all cells that are visible, according to the function you choose.

Does this help?

Kenshin,

Do a search in the help on Subtotal as a key word, this should give you what you are looking for

Graeme