How to sum a filtered column...

March 17, 2006 at 11:09:38
Specs: WinXP, P4/1025
Hi. I've got what I think is probably a very simple question about Excel. I want to know how to sum figures in an autofiltered column. So, I've got a long list of telephone calls, and I've autofiltered the whole sheet. When I select one of the drop-down options (for example, so it shows only calls to a particular number) and then sum that list, I get a total; but when I change the drop-down menu selection in the columns, the total changes!! This is very annoying, but I'm sure there must be a simple solution... ? Any ideas? Thanks.

See More: How to sum a filtered column...

Report •

March 17, 2006 at 15:53:30
You sum the total of a filtered list but when you change to another filter the sum changes.

As it should to sum the total of the newly filtered list.

Maybe what you want is separate cells with formulas to sum the different filtered items such as:
=sumif(G2:G20,"particular number")

(although I don't know how you would sum a phone number? Perhaps you are thinking of
=countif(G2:G20,Particular number"))


Report •

March 18, 2006 at 04:35:15
Sorry - I don't think I made myself very clear. I've got an itemised telephone bill on an Excel spreadsheet. I'm not trying to add the TELEPHONE NUMBERS, but the CHARGES for each number called. So, after autofiltering the whole sheet, I want to be able to enter onto the sheet the total for all the calls to a particular number; and I want to do this for quite a few of the numbers. So my first step was to select from the drop-down menu at the top of the column containing the telephone numbers a particular number called, which means that calls to all other numbers are filtered out. I then want to sum all of the calls selected (to the specific number selected), and I want that figure to STAY THE SAME even when I make a different selection, whereas at the moment it changes when I do so.

I'm afraid I'm not an expert on Excel, so when you say "(G2:G20, 'particular number')", I don't really know what you mean by 'particular number'. What do I actually type in that space?

Thanks again!

Report •

March 18, 2006 at 08:21:40
For what you are wanting to do perhaps sorting and sub-totaling would work better or even a pivot table.

Insert a row (if not already there) in Row A and place column header names to your data such as "Phone number called", "Call Duration" and "Cost of call"

Then select all of your data (Click on A1 or the top left active cell below your column header names, Hold down the Ctrl and Shift keys and hit the End key to select all the data.)

Now go to Data, Sort by the column with the phone numbers.

Now go to Data, Sub-totals and choose a break at each phone number and sum at same.

That should give you a sum of costs associated with each phone number called.

Pivot table: Select all the data as earlier noted and then go to Data, Pivot Table report and use it's wizard.
It may take you couple of tries to get what you want rather than me trying to explain it to you.

I believe Sort and Sub-Totals will get you what you are looking for with relative ease.


Report •

Related Solutions

March 20, 2006 at 07:30:00
Thanks very much for that. The first option worked fine for me. Since you're all so knowledgeable about this, I'm wondering if you know of a way to abstract subtotals from the list? Or, alternatively (which amounts to the same thing) hide everything on the table except for the subtotals? (I realise that I can do this simply by hiding each sub-list one by one, but I'm just wondering if there's a quick way of doing it... ?) Thanks again.

Report •

Ask Question