Automating filtered totals

**flux88** · 10-12-2010, 09:28 AM

Hi,

I have a massive spreadsheet of directory URLs and the number of pages there. I can only find out the number of pages under the lowest level directory.

e.g.
/website/pm/test/1/ ---- 5
/website/pm/test/2/ ----- 9
/website/pm/new/ --------2
/website/keyboard/ -------3pages
...

Basically I want to find out the total number of pages for /website/pm/. So that includes 3 folders. In the example the number I would be looking for is 16.

To do this I filtered by the criteria "/website/pm/" and a simple equation added them up. =SUBTOTAL(9,B:B)

But this will take FOREVER as there are so many different levels of directories I want to find out the totals of and have recorded in a seperate spreadsheet. So I need someway of automating it to an extent.

Any ideas?

Many thanks,

Niall.

**Andy Pope** · 10-12-2010, 09:40 AM

You maybe able to use a pivot table based on data and some helper columns.

Can you post a example workbook so we can see the real layout of the data.
Also include a few more records is possible.

**flux88** · 10-12-2010, 10:02 AM

I've attached a sample spreadsheet.

Just playing around with Pivot now.

**Andy Pope** · 10-12-2010, 10:11 AM

Try this example pivot table

**flux88** · 10-12-2010, 10:28 AM

Thanks that is exactly what I want.

However when I copy and paste my data and extend to helper cells the pivot chart doesnt update?

**Andy Pope** · 10-12-2010, 10:32 AM

Would not expect it to either.

You need to use the Pivot wizard to step back and adjust the range being used, you can then refresh the table.

You can use a dynamic range as the data source if you set it up.

PTDATA: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))