Excel filter/summary problem 1

[shteev]

I'm using Excel 2000.

I have a large (10000+ rows) table in one worksheet, and then a table which summarises it's results in a separte worksheet. I can use the Data/Filter/Autofilter function to filter my large table; but it only changes how the table is seen, and so the summary table is not changed. Is there a way in which I can apply a filter so that it will dynamically affect the summary table?

 

[ETID]

The pivot table is the dynamic filter of choice for me...

It's has a wizard and it's easy once you get the hang of it.
...and if you mess it up, you just delete it and start over.


the pivot is based on your raw data, but never alters it.

 

[henio]

Hi shteev,

if your summary sheet contains lots of SUM functions, replace them with SUBTOTALs. These will sum, count, etc whatever you have filtered.

hth

henio

 

[shteev]

Thx for your suggestions, but I still cant seem to summarize my data in the way I need to.

Here's an example of what I have:

Row ID# RatingA(1-10) RatingB(1-10) SubType
1 2 1 Type A
2 3 6 Type A
3 3 8 Type B
4 10 8 Type B

And here's the table I want to produce:

Total # of Total # of
Rating Rating A Rating B
1 0 1
2 1 0
3 2 0
4 0 0
5 0 0
6 0 1
7 0 0
8 0 2
9 0 0
10 1 0

This table is easy enough to produce, but I need a way to limit it dynamically; say, by only including rows of Type A, or Type B.

 

[01Patrik]

I'd use the database funtions - DCOUNT should do it.

It's easy to do, but hard to explain here - but I'll give it a shot:

In my example below I put the data in a new workbook, in 'sheet1', so that "Row ID#" will be in cell A1.

Write "Type A" in cell G1 (explanation below)

Then I put the table you want to produce (exactly like it looks in your example) in 'sheet2', with "rating" in cell A1.

Then I placed the following in 'Sheet3' A1-B11:

Subtype RatingA(1-10)
=Sheet1!$G$1 1
=Sheet1!$G$1 2
=Sheet1!$G$1 3
=Sheet1!$G$1 4
=Sheet1!$G$1 5
=Sheet1!$G$1 6
=Sheet1!$G$1 7
=Sheet1!$G$1 8
=Sheet1!$G$1 9
=Sheet1!$G$1 10

and the following in 'Sheet4' A1-B11:

Subtype RatingB(1-10)
=Sheet1!$G$1 1
=Sheet1!$G$1 2
=Sheet1!$G$1 3
=Sheet1!$G$1 4
=Sheet1!$G$1 5
=Sheet1!$G$1 6
=Sheet1!$G$1 7
=Sheet1!$G$1 8
=Sheet1!$G$1 9
=Sheet1!$G$1 10


Now, back to 'Sheet2', the table you want to produce:

In Cell B2 (The "total # of rating A" of "Rating 1" - in your example 0) you write the following function:

=DCOUNT(Sheet1!$A$1:$D$5;2;Sheet3!A$1:B2)

In cell C2:

=DCOUNT(Sheet1!$A$1:$D$5;3;Sheet4!A$1:B2)

In cell B3:

=DCOUNT(Sheet1!$A$1:$D$5;2;Sheet3!A$1:B3)-SUM(B$2:B2)

In cell C3:

=DCOUNT(Sheet1!$A$1:$D$5;3;Sheet4!A$1:B3)-SUM(C$2:C2)

Now copy the formulas in B3:C3 to all cells below (rows 4-11).


Now the table is displaying what you wanted: Type A only. By changing in 'sheet1'!G1 you can easily switch criteria to "Type B" etc.

Takes a couple of minutes to set up, but it's very easy to treat like a template once done. Just remeber that you need one new row in sheet 3 and 4 for every new row in sheet 1.

Good luck!


// Patrik

 

[01Patrik]

Forgot to mention a couple of things:

First, the "total # of Rating A" I treated like one cell, in 'sheet2'!B2. Same thing for the next one in 'sheet2'!B3.

Second, If your data contains any texts instead of numbers, use the DCOUNTA function instead.

Third, You can easily summarize the data instead of counting it by using the exact same template, but replacing (ctrl+H)COUNT with SUM (using function DSUM instead of DCOUNT).




// Patrik

 

[01Patrik]

...note that in order to see ALL you must set the criteria ('sheet1'G1) to <b>=""</b>. Simply deleting "Type A" won't do the trick.

And it is more practical to have the cristeria on 'sheet2' instead of 'sheet1' - it's a bit easier to see the effects that way...


// Patrik

 

[shteev]

Cheers for your help; I've only just been able to test it.

Initially I couldn't get your code working; you've accidentally written ';' instead of ',' in the DCOUNT statements. Once I figured that out, I managed to generate this table, which as you can see is not quite right:

Total # of Total # of
Rating Rating A Rating B
1 0 1
2 1 0
3 1 0
4 0 0
5 0 0
6 0 1
7 0 0
8 0 0
9 0 0
10 0 0

Now you've given me a pointer, tho, I can probably work it out for myself. Thanks!

 

[01Patrik]

Hi,

This is because its counting "Type A" only - I thougt that was what you wanted - the table is correct! write ="" in 'sheet1'!G1 and you'll get ALL the info counted.

The use of ';' instead of ',' is a setting which I prefer... Sorry about that.

// Patrik

 

[shteev]

Of course it's counting only 'Type A'.... Dopey me!!!

Everything's working fine now. Great!

 

[01Patrik]

No problem. Thanks for the pointy thing...

// Patrik